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.

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

Formula to calculate hours worked
Formula to calculate hours worked

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?

missing start times in Excel worksheet
missing start times in Excel worksheet

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:

  1. At the top left of the Excel window, click the File tab, then click Options.
  2. At the left, click the Advanced category
  3. 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”
  4. Click OK to close the Options window.
Excel Options window Advanced settings
Excel Options window Advanced settings

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. 😉

____________________________

3 thoughts on “Midnight Times Missing in Excel Worksheet”

  1. 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. 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 Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.