Noon and midnight

If you’re like me, whenever you see “12:00 pm” or “12:00 am” you need to stop and think for a moment as to whether that’s noon, or midnight. I know I’m not the only one this happens to, since at work there was recently a 10am – 12am shift made available, and I guarantee you that none of our shifts are 14 hours long.

So if you want your spreadsheet to display “Midnight” and “Noon” instead of 12:00 AM or PM, you can do it pretty easily using an if() statement.

Before I give you the formula, let me remind you of how dates and times work. The date January 1, 1900 is stored as “1” in most programs. Technically, actually, that’s January 1, 1900 at midnight (the starting one, not the ending one). 1.25 would be January 1, 1900 at 6:00 AM, since 6 hours and no minutes divided by 24 hours in a day is 6/24 = 0.25.

You may also remember the hour() and mod() functions. The first one, hour(), returns the hour from a date or time, so hour(1.25) would return 6. The second one, mod(number1,number2), returns the remainder if number1 is divided by number2. So =mod(1.25,1) would return 0.25.

Generally speaking, the hour for midnight is 0. Outside if the military people usually resort to saying 12:30 AM or some such, but in the military and other places that use 24 hour clocks it would be 00:30, which is what leads to the colloquial term of oh-dark-thirty. Therefore the if() statement to check to see if it’s between midnight and 1 AM would be =if(hour(time)=0,”yes”,”no”)

The hour for noon is always 12, so the formula to see if a time is between noon and 1 PM would be =if(hour(time)=12,”yes”,”no”).

That’s great if you’re just working with whole hours, or you really only care if it’s somewhere in that range. But if you wanted to see if it was exactly noon then you’d actually have to do =if(AND(hour(time)=12,minute(time)=0,second(time)=0),”yes”,”no”).

That’s a pretty cumbersome formula, but fortunately since we know how times are stored in a spreadsheet, there’s a simpler way, using mod(). Since 1/1/1990 at midnight is 1, there’s no remainder if you divide it by 1, so mod(time,1)=0 means it’s midnight. Similarly, 1/1/1990 at noon would be 1.5, so if you divide it by 1 the remainder is 0.5, so mod(time,1)=.5 means it’s noon.

So if you want to combine these into a single formula, it might look like: