{"id":25,"date":"2026-10-02T16:40:00","date_gmt":"2026-10-02T16:40:00","guid":{"rendered":"https:\/\/military-time-chart.org\/blog\/?p=25"},"modified":"2026-10-04T16:37:03","modified_gmt":"2026-10-04T16:37:03","slug":"military-time-in-excel-and-google-sheets","status":"publish","type":"post","link":"https:\/\/military-time-chart.org\/blog\/military-time-in-excel-and-google-sheets\/","title":{"rendered":"Military Time in Excel and Google Sheets"},"content":{"rendered":"

TL;DR:<\/strong> Store true time values, display with custom format hhmm or hh:mm, and avoid typing military strings as plain text if you need sorting and duration math.<\/p>\n

Enter 24-hour times correctly<\/h2>\n

Type 14:30 with colon; Excel stores fraction of day. Format cell Custom \u2192 HHMM for 1430 display without colon if required by SOP.<\/p>\n

Text 1430 vs time value<\/h2>\n

Text sorts wrong (0900 before 800 lexically). Convert text to time with TIME(HOUR,MIN,0) helper columns when importing legacy CSV.<\/p>\n

Google Sheets locale<\/h2>\n

Spreadsheet locale affects default time parsing. Set document locale explicitly for multinational shared sheets.<\/p>\n

Duration across midnight<\/h2>\n

Shift 2300\u20130700 duration = (1 \u2212 start + end) mod 1 in Excel time math\u2014use established formula template, do not eyeball.<\/p>\n

AM\/PM contamination<\/h2>\n

Imported US-formatted CSV may label 3:00 PM strings. Clean with parser before applying military display format.<\/p>\n

Conditional formatting bands<\/h2>\n

Highlight cells outside allowed window (before 0600 or after 2200) for compliance dashboards.<\/p>\n

Print-ready roster export<\/h2>\n

PDF export preserves HHMM formatting if page setup uses monospace column width\u2014test print before posting wall roster.<\/p>\n

Validate against web tool<\/h2>\n

Spot-check five random converted cells against military-time-chart.org<\/a> when building first template.<\/p>\n

Macro pitfalls<\/h2>\n

VBA AutoFormat may revert to AM\/PM on save\u2014lock template as .xlsx with protected format styles.<\/p>\n

Sharing with non-technical staff<\/h2>\n

Hide formula columns; show only military display column to reduce accidental edits.<\/p>\n

TRUE time type not text<\/h2>\n

Store Excel time values; display HHMM custom format\u2014sorting works correctly.<\/p>\n

TIMEVALUE repair column<\/h2>\n

Legacy CSV text 1430 \u2192 =TIMEVALUE(LEFT(A1,2)&”:”&RIGHT(A1,2)).<\/p>\n

Midnight duration formula template<\/h2>\n

2300\u20130700 shift duration use established mod-1 day fraction template\u2014do not eyeball.<\/p>\n

Protect template cells<\/h2>\n

Lock format ranges; allow entry column only\u2014prevent manager ‘fix’ breaking display.<\/p>\n

Validate five cells post-fix<\/h2>\n

Spot-check repaired sheet against https:\/\/military-time-chart.org\/ before distributing roster.<\/p>\n

<\/p>\n

Payroll cell that stores 14:30 as a real time<\/h2>\n

A payroll sheet that will subtract lunch must store 14:30 as a time, not as the text 1430. Type 14:30. The cell’s underlying value is a fraction of a day. Format it with a custom format if the display must look like 1430 for a legacy export. The display and the value are different decisions. A coworker who types the four digits as text has made a label. Labels cannot be subtracted from 17:00 to get hours worked.<\/p>\n

Test the cell by adding it to a blank time and by subtracting it from 22:00. If the sheet returns a number of hours, the cell is a time. If it returns a #VALUE! or a concatenation, the cell is text. Fix the type before you fill the column down. Filling text down produces a beautiful column of stamps that still cannot pay anyone.<\/p>\n

Text 1430 that will not cross midnight<\/h2>\n

A night guard who works 22:00 to 06:00 cannot be computed by subtracting text stamps, and cannot be computed by subtracting real times either unless the formula knows the end is on the next day. Store both as times, then use a formula that adds a day when the end is earlier than the start. The expected duration is eight hours. A result of negative sixteen hours means the wrap was ignored. Do not abs() it and hope. Absolute value hides the bug and will also hide a genuinely reversed shift.<\/p>\n

Put the formula in a template cell with a comment that names the wrap. “Adds one day when out is earlier than in.” Lock the formula cell. People who are good at clocks and new to sheets will overwrite the formula with the number 8 because they already did the math in their head. Their math may be right today and wrong on the shift that is six hours. The formula is the record.<\/p>\n

Locale comma that breaks 14:30 on import<\/h2>\n

A workbook opened under a locale that expects 14.30 or 14,30 may turn 14:30 into text or into a date. Before you share the file, set the time format explicitly and put a note in the cover cell: “times are hours and minutes, colon separator, 24-hour.” On import, check one known cell. The known cell for this template is the lunch row, 14:30, which should display as half past two in the afternoon and should still subtract cleanly from 15:00 to make thirty minutes.<\/p>\n

If the import created a date in 1900 or a fraction you do not recognize, stop and fix the import. Do not format over it until you know the value. A wrong value with a pretty 1430 format is worse than an obvious error, because payroll will trust the pretty cell.<\/p>\n

Five-cell check against a known afternoon<\/h2>\n

After any template change, check five stamps by hand: 06:00, 12:00, 14:30, 18:45, and 00:15. You know what they mean. 14:30 is the one that catches a secret AM\/PM conversion, because a converter left in twelve-hour mode might show 2:30 PM while the cell is right, or might have shifted the value. Compare the value, not the prettiness. Write the five results in the change log. A template without a change log will be “fixed” again next month by someone who did not know you already fixed the wrap.<\/p>\n

Add one wrap case to the same log: in 22:00, out 06:00, expected eight hours. If the five pretty stamps pass and the wrap case fails, you do not ship the template. The pretty column is how the bug hides. The wrap case is how you refuse to hide it. Date the log row with the file name so the next editor can find the test instead of inventing a new one.<\/p>\n","protected":false},"excerpt":{"rendered":"

Format cells and formulas for HHMM display without locale errors.<\/p>\n","protected":false},"author":1,"featured_media":13,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[3],"tags":[],"class_list":["post-25","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-conversion-charts"],"_links":{"self":[{"href":"https:\/\/military-time-chart.org\/blog\/wp-json\/wp\/v2\/posts\/25","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/military-time-chart.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/military-time-chart.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/military-time-chart.org\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/military-time-chart.org\/blog\/wp-json\/wp\/v2\/comments?post=25"}],"version-history":[{"count":6,"href":"https:\/\/military-time-chart.org\/blog\/wp-json\/wp\/v2\/posts\/25\/revisions"}],"predecessor-version":[{"id":171,"href":"https:\/\/military-time-chart.org\/blog\/wp-json\/wp\/v2\/posts\/25\/revisions\/171"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/military-time-chart.org\/blog\/wp-json\/wp\/v2\/media\/13"}],"wp:attachment":[{"href":"https:\/\/military-time-chart.org\/blog\/wp-json\/wp\/v2\/media?parent=25"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/military-time-chart.org\/blog\/wp-json\/wp\/v2\/categories?post=25"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/military-time-chart.org\/blog\/wp-json\/wp\/v2\/tags?post=25"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}