Midnight Times Missing in Excel Worksheet
Last week, I was working on a client’s time sheet file, and noticed something strange. The worksheet was used to record shift start and end times, and I was testing the calculations, to make sure that everything was working correctly.
Shifts that run past midnight can cause problems, so I tested that scenario, and I also tested shifts that started at midnight. I wanted to be sure that everyone would get all the pay they were entitled to!
In column D, I calculated the hours worked, by subtracting the start time from the end time, and adding 1 to the end time, if it was in the next day. Here is the formula in cell D2:
The calculations were working well, but the cells where I had entered a midnight time appeared empty. Fortunately, the calculations still worked, even with the missing start times.
I know that midnight is a favourite time for mysterious things to happen in horror movies, but was my worksheet haunted? Was this an early Halloween prank?
Then I realized that someone had formatted the worksheet to hide the zeros. And, if your worksheet is formatted to hide zeros, the midnight times won’t show, because they are equal to zero – 0:00
I turned the Show Zeros setting back on, and all the midnight times came out of hiding.
To show zeros on a worksheet:
- In Excel 2010, click the File tab, then click Options.
- In the Advanced category, scroll down and in the Display options for this worksheet, add a check mark to "Show a zero in cells that have zero value"
- Click OK to close the Options window.
Unhide Your Zeros
I had forgotten all about this problem, until someone left a comment on my blog yesterday, asking why the midnight times weren’t showing in her worksheet. At least I’m not the only one who has had this mysterious problem, and if it has happened to you, I hope this tip helps.
It’s not one of Excel’s most complicated problems, but it’s the little things that can drive you crazy. ;-)