How Dates Work in Excel – The Calendar System Explained + Video
Bottom line: With Valentine's Day rapidly approaching I thought it would be good to explain how you can get a date with your Excel skills. Just kidding! 🙂 This post and video explain how the date calendar system works in Excel.
Skill level: Beginner
Dates in Excel can be just as complicated as your date for Valentine's Day. We are going to stick with dates in Excel for this article because I'm not qualified to give any other type of dating advice. 🙂
Video Tutorial on How Dates Work in Excel
The following is a video from The Ultimate Lookup Formulas Course on how the date system works in Excel.
There are over 100 short videos just like the one above included in the Ultimate Lookup Formulas Course.
This course has been designed to help you master Excel’s most important functions and formulas in an easy step-by-step manner.
What is a Date in Excel?
I should first make it clear that I am referring to a date that is stored in a cell.
The dates in Excel are actually stored as numbers, and then formatted to display the date. The default date format for US dates is “m/d/yyyy” (1/27/2016).
The dates are referred to as serial numbers in Excel. You will see this in some of the date functions like DAY(), MONTH(), YEAR(), etc.
So then, what is a serial number? Well let's start from the beginning.
The date calendar in Excel starts on January 1st, 1900. As far as Excel is concerned this day starts the beginning of time.
Each Day is a Whole Number
Each day is represented by one whole number in Excel. Type a 1 in any cell and then format it as a date. You will get 1/1/1900. The first day of the calendar system.
Type a 2 in a cell and format it as a date. You will get 1/2/1900, or January 2nd. This means that one whole day is represented by one whole number is Excel.
You can also take a cell that contains a date and format it as a number.
For example, this post was published on 1/27/2016. Put that number in a cell (the keyboard shortcut to enter today's date is Ctrl+;), and then format it as a number or General.
You will see the number 42,396. This is the number of days that have elapsed since 1/1/1900.
Date Based Calculations
It is important to know that dates are stored as the number of days that have elapsed since the beginning of Excel's calendar system (1/1/1900).
When you calculate the difference between two dates by subtraction, the result will be the number of days between the two dates.
1/27/2016 – 1/1/2016 = 26 days
6/30/2016 – 1/1/2016 = 181 days
There are a lot of Date functions in Excel that can help with these calculations. Last week we learned about the DAY function for month-to-date calculations with pivot tables.
We won't go into all the date functions here, but understanding that the serial number represents one day will give you a good foundation for working with dates.
What About Dates with Times?
Do you ever work with dates that contain time values?
These dates are still stored as serial numbers in Excel. When you convert the date with a time to the number format, you will see a decimal number.
This decimal is a fraction of the day.
One hour in Excel is represented by the number: 1/24 = 0.04167
One minute in Excel is represented by the number: 1/(24*60) = 1/1440 = 0.000694
So 8:30 AM can be calculated as: (8 * (1/24)) + (30 * (1/1440)) = .354167
An easier way to calculate this is by typing 8:30 AM in a cell, then changing the format to Number.
So if you are running a half hour late and want to let your boss know, text him/her and say you will be there at 0.354167. 🙂
Checkout my article on 3 ways to group times in Excel for more date time based calculations.
Don't Talk About Excel Dates with Your Date
Unless your Valentine shares a similar passion for Excel, I strongly recommend NOT sharing this information on your date.
I remember the first time I met my wife, and told her I worked in finance. The first word out of her mouth was, “BORING!”. Awe… it was love at first sight… LOL 🙂
But you should now be able to use Excel to determine how many days it has been since you last spoke to your date. That's the only dating advice I can give.
Please leave a comment below with any questions on Excel dates. Thanks!