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.
The Ultimate Lookup Formulas course is now part of our comprehensive Elevate Excel Training Program.
Click Here to Learn More About Elevate Excel
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!
Excel is using 2 date systems.
1904 based date system and reason why it exists would be a good subject for the next post. Several times I received files crated in that system and had a hard time because such change in the setting is not the first thing that comes to mind when you have problems with the data. It’s even worse if you are not aware that 1904 based date system exists.
Hi Leonid,
Great point! The 1904 date system is typically used by Excel for Mac versions. The starting date of Jan 1, 1904 does make it confusing when you copy/paste dates between workbooks on different date systems.
Here is a link the Microsoft help article that explains more about the issue and how to fix it. https://support.microsoft.com/en-us/kb/214330
Thanks again Leonid! 🙂
Perhaps you don’t know, but Excel’s WEEKDAY function (which finds the week day of a given date) has some bugs.
Read all about it (with my solutions) here:
http://meniporat.blogspot.co.il/2012/07/excel-calculating-weekday-for-given-date_2236.html
Jon, found you through the wonders of YouTube. You sir are a godsend.
Most of my questions are answered through your pivot table series. I want to know if I can use a drop down or search feature with the slicer on the summary page instead of just the slicer buttons. This is because I have a huge “sales staff” of 200 + so that list of slicers would just be really ineffective. So, any drop down menu to slice for me????
Thank you Kati! You just inspired an idea for next week’s blog post. There is no search feature built into slicers, but I have a fairly easy workaround that should do the trick. Stay tuned… 😉
Love learning!!!
Question…..
We have a current worksheet that we have a begin date and end date. Then we have a column that changes it to Years, Months, Days.
=DATEDIF(G5,H5,”y”) & ” years, ” & DATEDIF(G5,H5,”ym”) & ” months, ” & DATEDIF(G5,H5,”md”) & ” days”
We found that the data we are looking at had some who returned maybe 1 or 2 times, so there are several columns that are either begin date, end date that have ended up to be with 4 columns of Total “Years, Months, Days” in each cell. Now we would like to total up ALL 4 columns for a total amount of the years, months, days spent, but it continues to come up with an error. How can we get that total amount? (end result that is wanted is how long someone was in our care no matter how many times they returned)
Hi Jon, is there a way that dates like 8/8/16 are in one column, in the next, it is August (as in August as text)? I know I can use the copy, paste special, values, but I hope to have it automatic through a formula. The reason I need this is I want to use the countif function for each month, like using “august” as my criteria. Thanks a million.
Hi Melissa,
Great question! We can use the TEXT function for this. If your date is in cell A2, put the following formula in cell B2.
=TEXT(A2,”mmmm”)
The TEXT function converts the number in A2 to a specific format. In this case we can use the “mmmm” format to return the full month name, August.
We can also use “mmm” to return the abbreviated month name, Aug.
I hope that helps. Thanks!
It worked! That was perfect and easy. Thank you!
I know that this is an old post, but this is information I’ve been looking for for the better part of a month, so THANK YOU
If there are blank cells in my 1st column, the 2nd column where the formula =text(A2,”mmmm”) returns January as an entry. How can I make so excel just leaves it blank? Thanks!
I would use and IF statement
=IF(A2=””,””,TEXT(A2,”mmmm”))
Very nice training like this and I am your became your fan
Thanks for your support Partheeban!
Hello Jon,
I want to calculate the different in between two date including time.
E.g: want to found exactly time for the perticular task.
processing starting Date
01/25/2016 09:00:00 AM
processing end time Date
01/26/2016 15:30:00 PM
how i can calculated exact time need for this task.
only business hours should be calculated.
working hrs.
09:00:00 AM to 18:00:00 PM
Thanks,
Vaibhav
Hi Vaibhav,
Great question! There is no easy answer for this one. My friend Dave has an article on how to calculate the net work hours between two dates. If you are looking to include times then the formula gets more complex. Here is another article with a solution. I have not tried this one but should work.
Thanks Jon….
G’day Jon, I wish to use the date in Excel as a sequential number, as per examples:
25 November 2016 or 25/11/2016 as a sequential number 61125
02 December 2016 or 02/12/2016 as a sequential number 61202
This process is particularly convenient for tracking documents in date order.
Your advice would be appreciated,
Richard
Hi Richard,
The dates are already stored as sequential numbers. Typically we see date values formatted as dates with the mm/dd/yyyy or dd/mm/yyyy formatting applied.
To view the dates as numbers we just need to change the cells format to General or a Number format. Here is a screenshot.
I hope that helps.
When entering a date as 15jan15 Excel accepts this as 2015. When entering a date as 15jan16 Excel changes this to 1916. How do I tell Excel that we are now in the 21st century please?
Hi David,
I’m not sure. I get 2016 when I enter it. What version of Excel are you using?
Thank you for this reply.
I am using Excel 2013, but do not know how to find what version it is.
An office where I do volunteer work also uses Excel 2013, but does not have the same problem.
I was wondering if it is possible to amend the start date of 1 January, 1900 in some way to overcome this.
I remember, a long time ago, that in Lotus 1-2-3 we could enter a year as 101 and Lotus would reply with 2001.
This doesn’t appear to be the same in Excel.
Thanks and I look forward to your reply
Hi David,
I did some further investigation and it’s a Windows setting. The following page has steps on how to change the setting in Windows.
https://support.office.com/en-us/article/Change-the-date-system-format-or-two-digit-year-interpretation-e6354061-6c98-4e17-84b4-f122dc6063a7
Go to the section on the page with the heading: Change the way two-digit years are interpreted
I hope that helps.
Hi Jon,
Many thanks for this, this is exactly what I wanted. This had been annoying me for some time, and the change is so simple when you know where to look.
Once again, thanks.
Hi. I was having an issue where the date was taken by the system and stored as a different time format. It was exported to Excel. In the file, the date is formatted to 3/3/2017 1:07:09 AM and the other one is 03/17/2017 21:48:29. I tried to group it under Pivot table but having an error “Cannot group that selection”. Please help!
Just noticed this:
If you enter 12/31/29, Excel assumes you mean 12/31/2029
but enter 12/31/30, Excel assumes you mean 12/31/1930.
Any idea if this is a setting or just the way Microsoft programmed Excel? I just found this interesting….
Thank you for your easy to grab tips on Excel, We appreciate.
Keep on doing the good job!
Really interesting thank you
DAYS360 function returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.
Old post, but still because it rankes high in google:
Excel incorrectly counts the non-existing day 2/29/1900 as day number 60, so if you are converting a date to/from the day number outside of Excel you have to account for this.
I am struggling with dates in excel on my mac. the “long date” format for some reason only includes the last two digits of the year (so 2019 becomes just ’19’) and I can’t for the life of me figure out how to change this. Help please!!!
I love the Valentine’s Joke xD
haha #relatable
how to use the following syntax : =if(a1″”;if(b1″”;b1;NOW());””) in excel android on my mobile telephone. Thanks in advance
how do I determine if a day in a cell (e.g today)has a even or odd end?
thanks for sharing its a very informative and helpful.
Excel is WRONG for the dates before March 1900, because it calculates as if 1900 was bissextile, but it was NOT!
So day 1 is actually December 31, 1899, and not January 1, 1900, as Excel wrongly displays it, and display day 0 as 0 January…
LibreOffice Calc and Google Sheet do it right, and display previous dates using negative numbers.
Excel never corrected the mistake.
Conclusion: the days are actually calculated from 30 December, 1899 as day 0
Great, date is stored as a number format. I have tried to use excels substring (=left) to extract month and year, and I get an integer! I know why now but fixing it isnt any more frustrating…
Strange: I have excel file with date stored as general, displayed as 44494 or 2021-10-25 when switched to Date
When I use TSQL to do the conversion:
select dateadd(d,44494,’1900-1-1′)
I get
2021-10-27
Why the diff?