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

Learn How Dates Work in Excel

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.

Watch the Video on YouTube

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.

Click Here to Learn More About the Ultimate Lookup Formulas Course

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

Excel Dates Are Stored as Serial Numbers and Formatted as Dates

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.

Date Functions Have a Serial Number Argument for a Date Value

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.

Excels Date System Starts on January 1st 1900

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.

Keyboard Shortcut to Enter Todays Date Excel Ctrl Semicolon

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.

Calculate the Difference Between Two Dates in Excel - Returns Number of Days

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.

Ust the DAY Function to Return Day of Month in the Pivot Table Source Data

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.

The Time Value is Stored as a Fraction of the Serial Number 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!

28 comments

Your email address will not be published. Required fields are marked *

  • 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….

  • 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!

  • 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?

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

      Convert Date to Number Format in Excel

      I hope that helps.

  • 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

  • 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!

  • 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!

  • 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)

  • 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… 😉

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

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly