TL;DR: Cells showing ######## or converting 1430 to decimal 0.59 mean wrong format type. Apply Custom HHMM or convert text to time with VALUE/TIMEVALUE functions.
Hash marks column too narrow
Widen column first—sometimes value correct but display hidden.
Decimal instead of clock
0.604166667 is internal fraction—Format Cells → Time or Custom [h]mm if duration exceeds 24h.
Text 1430 won’t sum
Sum of text gives zero—convert: =TIMEVALUE(LEFT(A1,2)&”:”&RIGHT(A1,2)) for HHMM text legacy.
CSV import stripped leading zero
800 in CSV—use Power Query pad step or TEXT(A1,”0000″) display column.
Regional semicolon formulas
EU Excel uses semicolon in TIME function—share locale-aware template not US-only macro.
Conditional format midnight wrap
Highlight shifts crossing midnight needs formula (END<START)—not simple greater-than rule.
Chart axis time scale
XY chart with time horizontal axis requires true datetime cells—text HHMM charts lie.
Validate fix batch
After formula fill, spot five cells against military-time-chart.org human reference.
Protect template
Lock format cells, allow data entry only in input column—prevent well-meaning manager “fix” breaking format.
Document repair steps
Wiki page “roster looks wrong” → widen, format, text-to-time flowchart—reduce IT tickets.
Widen column before panic
######## may hide correct time—widen first.
Power Query pad step
CSV import 800 → pad to 0800 in transform pipeline.
Midnight conditional format
Shift crossing midnight needs (END Roster looks wrong → widen, format, text-to-time—reduce IT tickets. EU Excel TIME function separators differ—share locale-aware template. A cell full of hash marks often means the column is narrower than the formatted time. 18:45 can be sitting underneath, correct and invisible. Widen the column before you touch the value. If widening reveals 18:45, you are done with that cell. If widening reveals a date in 1900 or a fraction, you have a value problem and the width was a coincidence. The order is widen, look, then decide. Re-entering 18:45 into a hash-mark cell without looking will overwrite a good value with whatever you remember. Check the neighbor cell 12:00 the same way. A column that hashes 18:45 and shows 12:00 is narrow for the longer display, not corrupt. A column that hashes both is either extremely narrow or actually broken. The pair tells you which. One cell cannot. Excel stores 18:45 as a fraction of a day. 18.75 hours divided by 24 is 0.78125. If you see 0.78125, format as time before you assume the sheet forgot the appointment. The appointment may be there. A coworker who “corrects” 0.78125 by typing 1845 as text has destroyed a real time and left a label. Format first. The format you want shows 18:45. Then lock the format so the next paste does not bring General back. Keep a note in the header: “0.78125 is 18:45, do not retype.” People fear fractions and they retype. The note is for them. It can cite one example only. A note that lists every fraction is a second spreadsheet. One example teaches the pattern. A CSV export may strip the leading zero, and a later import may strip the colon, leaving 805. 0805 is five minutes past eight in the morning. 805 is a three-digit string that will sort badly and may be read as 8:05 or as 20:05 by two different importers. Restore the zero in a text pass that checks length. Four digits, or a colon form with a leading zero. Reject 805 into a quarantine column. Do not auto-guess. Guessing is how half the rows become evening. The quarantine check is a known morning: the source had 08:05, the file should not contain 805 or 8:05 without a leading zero if your standard is four digits. One known row earns the right to quarantine the rest. A quarantine with no known row is a pile. A chart built on text stamps will plot “1845” near “1900” in alphabetical space, which might look fine, and will plot “900” in a ridiculous place. Convert the axis to real times, then check that 18:45 sits after 12:00 and before 21:00. If the point for 18:45 appears before noon, the axis is still text or the value is still a fraction being read as a day count in the wrong unit. Fix the axis source. Do not drag the point. A dragged point is fan fiction. The quarantine tab should be named for the failure, “805-needs-a-zero,” not “misc” and not “fixed.” 805 is the three-digit leftover of 08:05. A name that states the repair tells the next person what not to do. They must not add a trailing zero. They must add a leading zero or restore the colon form, then move the row back only after 0805 or 08:05 survives a reload. Count the quarantine before and after the repair. If you started with forty rows and ended with forty in the live sheet plus six still quarantined, say so. A repair that “clears the tab” by deleting the six is not a repair. Those six are still someone’s morning. 08:05 is one of them. Deleting it makes the sheet look obedient and the yard late.Wiki flowchart for staff
Locale semicolon formulas
Hash marks that hide a real 18:45
Fraction 0.78125 that should be 18:45
CSV that turned 08:05 into 8:05 and then into 805
Chart axis that plots text 1845 at the wrong x
Quarantine tab named 805-needs-a-zero