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.
Time Past Midnight
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:
=(C2+IF(OR(C2=0,C2<B2),1,0)-B2)*24
Missing Midnight
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?
Hidden Zeros
Finally, 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
To fix the problem, I turned the Show Zeros setting back on, and all the midnight times came out of hiding.
To show zeros on a worksheet
Here are the steps I followed, to show the worksheet zeros:
- At the top left of the Excel window, click the File tab, then click Options.
- At the left, click the Advanced category
- Then, 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. 😉
____________________________
That’s one of those settings I forget about, but I often use the custom format “0;-0;” or something similar to hide zeros. It causes the same problem, so thanks in advance for hair-loss prevention.
This solved one of my problems in my time sheet. But, when someone isn’t working, the total hours column shows “24.00”. How do I get it to show “0.00”?
Thank you ever so much!!! This totally helped.