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:

=(C2+IF(OR(C2=0,C2<B2),1,0)-B2)*24

showzerovalues02

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.

showzerovalues03 

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

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:

  1. In Excel 2010, click the File tab, then click Options.
  2. 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"
  3. Click OK to close the Options window.

showzerovalues01 

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. ;-)

____________________________

You may also like...

2 Responses

  1. Doug Glancy says:

    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.

  2. Troy Canfield says:

    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”?

Leave a Reply to Troy Canfield Cancel reply

Your email address will not be published. Required fields are marked *