By Bob Umlas, Excel MVP
After reading this blog you should be an expert in Excel’s Dates and Times.
If you enter the number 1 in cell I1, for example, then look at the Home tab’s Number Format’s dropdown, you’ll see this (without the arrows!):
Notice that the number 1, formatted as a short date (dates as month/day/year), is January 1, 1900! (Also notice the time is 12:00:00 AM, but I’ll cover that later). Well, if 1 is January 1, what’s 0? Yes, it’s January 0 (not December 31, 1899)!
Excel stores dates as the number of days since January 0, 1900.
This article was written on April 11, 2017. Look:
What’s interesting here is the date expressed as a number: it’s 42836. That’s 42,836 days since January 0, 1900! So, Excel stores dates as numbers, but these numbers are simply formatted to look like dates. This version of a date, formatted as a number, is called a Serial number. The number 200,000, formatted as a date, is July 30, 2447!!
So now Excel can work with dates as simply as it does with numbers. Try finding out how many days old you are: You would enter a formula like =TODAY()-“5/6/42”. What you’d see probably makes no sense, at first: 12/6/1974, in my case! What’s that? It’s a number formatted as a date. The actual number of days is 27369:
You just need to format it as General. You can avoid the need to reformat the cell by using the DATEDIF function shown below. By the way, why did I use quotes around the date (=TODAY()-“5/6/42″)? If I hadn’t, it’d be treated as 5 divided by 6 divided by 42, not the date 5/6/42!! As a validity check, see the difference between entering these two: 5/6/42 or =5/6/42. You might even try entering =1*”5/6/42” – this too will give you the Serial number of 15467.
Excel has many functions to work with dates: MONTH, DAY, YEAR, TODAY, DATEVALUE, NETWORKDAYS, WEEKDAY, WORKDAY, DATEDIF and others. Using 4/11/17 as our date (and say it’s in cell A1), then here’s a sampling of these functions:
If you have =TODAY() in a cell and it returns 4/11/2017, then if you open the same workbook the next day it will say 4/12/2017. If you simply open the workbook and close it, you will be asked if you want to save changes. Why? You didn’t change anything! It’s because the TODAY function (among others) is a volatile function and is “always updating”.
Dates can be formatted in many ways. Here are some common ones, all using 4/11/17:
Suppose you have 4/11/17 in cell A1 and you want to refer to it from another cell, but with some text. You want it to say “Today is 4/11/17”, so you try =”Today is “&H1 but you get a surprise:
You need a new function for formatting that date, since cell H1 actually contains 42836, not 4/11/2017! Here’s the solution:
It’s the TEXT function. Here’s where you supply the formatting as in the table above. One more example (refer to the table above):
Okay, now let’s look at TIME. Time is similar to Date – it’s all formatting. Time is kept as a fraction of a day. Have a look at this initial example:
Notice that the formula in cell G3 is =G1=G2, and it’s returning TRUE. This means 6 PM is equivalent to .75. And similarly, column H shows 12 PM is equivalent to .5. There are 24 hours in a day. 12 PM is half-way through the day, or .5. 6 PM is ¾ through the day, or .75! What about 3:30 PM?
So, time is stored as a fraction of a day. Midnight is 0. This exact moment (it’s now about 3:37PM) is shown here via the NOW function:
It’s all formatting! Cell J1 is formatted as m/d/yyyy h:mm.
Here are some TIME built-in functions:
Here’s an anomaly you may have come across. Look at this spreadsheet:
The value in cell B7, summing the values in B1:B5, clearly seems wrong! In fact, it’s right, but it’s formatted wrong! Since time doesn’t go beyond 23:59:59 (1 second before midnight), once you would hit 24:00:00 (invalid), it’d be reset to 0. So, the value in B7 is right and would be displayed as you see in cell B9. Regular formatting of h:mm forces hours to be 0-23 only. The trick in cell B9 is to use the format of [h]:mm! The [h] overrides the hours to not rewrap at 24. The formatting as minutes (mm) also has an override of [mm], as does seconds [ss].
Here’s the display of cell B7 shown with [mm] and [ss] formats:
You are now officially an Excel Date & Time expert!
If you are interested in bringing Excel MVP Bob Umlas into your company (either virtually or traditionally) to do Excel consulting and/or training work, contact email@example.com.
About the Author
Bob Umlas has been using Excel since its inception in 1986. He has had more than 300 articles published on subjects ranging from beginner to advanced, VBA, tips, shortcuts, and general techniques using virtually all aspects of Excel. He is the longest running Microsoft Excel MVP in the world (25 years). He has been the technical editor of (about 20) of Bill Jelen’s (MrExcel) Excel books. Bob speaks at Excel conferences around the world on his favorite topic, Tips & Tricks, which wows even the experts. He is the author of 3 Excel books and is the current leader of NYC’s Excel Special Interest Group (which meets on the 2nd Tuesday of each month).