TL;DR: 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.
Enter 24-hour times correctly
Type 14:30 with colon; Excel stores fraction of day. Format cell Custom → HHMM for 1430 display without colon if required by SOP.
Text 1430 vs time value
Text sorts wrong (0900 before 800 lexically). Convert text to time with TIME(HOUR,MIN,0) helper columns when importing legacy CSV.
Google Sheets locale
Spreadsheet locale affects default time parsing. Set document locale explicitly for multinational shared sheets.
Duration across midnight
Shift 2300–0700 duration = (1 − start + end) mod 1 in Excel time math—use established formula template, do not eyeball.
AM/PM contamination
Imported US-formatted CSV may label 3:00 PM strings. Clean with parser before applying military display format.
Conditional formatting bands
Highlight cells outside allowed window (before 0600 or after 2200) for compliance dashboards.
Print-ready roster export
PDF export preserves HHMM formatting if page setup uses monospace column width—test print before posting wall roster.
Validate against web tool
Spot-check five random converted cells against military-time-chart.org when building first template.
Macro pitfalls
VBA AutoFormat may revert to AM/PM on save—lock template as .xlsx with protected format styles.
Sharing with non-technical staff
Hide formula columns; show only military display column to reduce accidental edits.
TRUE time type not text
Store Excel time values; display HHMM custom format—sorting works correctly.
TIMEVALUE repair column
Legacy CSV text 1430 → =TIMEVALUE(LEFT(A1,2)&”:”&RIGHT(A1,2)).
Midnight duration formula template
2300–0700 shift duration use established mod-1 day fraction template—do not eyeball.
Protect template cells
Lock format ranges; allow entry column only—prevent manager ‘fix’ breaking display.
Validate five cells post-fix
Spot-check repaired sheet against https://military-time-chart.org/ before distributing roster.
Payroll cell that stores 14:30 as a real time
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.
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.
Text 1430 that will not cross midnight
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.
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.
Locale comma that breaks 14:30 on import
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.
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.
Five-cell check against a known afternoon
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.
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.