How Dates Work in Excel - The Calendar System Explained + Video - Excel Campus
23

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!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 23 comments
David Evans - December 13, 2016

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?

Reply
    Jon Acampora - December 19, 2016

    Hi David,
    I’m not sure. I get 2016 when I enter it. What version of Excel are you using?

    Reply
      David Evans - December 19, 2016

      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

      Reply
Richard MOON - December 1, 2016

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

Reply
    Jon Acampora - December 9, 2016

    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.

    Reply
vaibhav - November 28, 2016

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

Reply
Partheeban - November 27, 2016

Very nice training like this and I am your became your fan

Reply
Melissa - September 26, 2016

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!

Reply
    Amy - December 7, 2016

    I would use and IF statement
    =IF(A2=””,””,TEXT(A2,”mmmm”))

    Reply
Melissa - September 25, 2016

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.

Reply
    Jon Acampora - September 26, 2016

    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!

    Reply
      Melissa - September 26, 2016

      It worked! That was perfect and easy. Thank you!

      Reply
Jennie - July 27, 2016

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)

Reply
Kati - February 11, 2016

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

Reply
    Jon Acampora - February 11, 2016

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

    Reply
Meni Porat - January 29, 2016

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

Reply
Leonid - January 27, 2016

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.

Reply
    Jon Acampora - February 1, 2016

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

    Reply

Leave a Reply: