The objective of this post is to teach you how Excel handles date and time and provide you with all the tools you will need.

It’s designed to be read in conjunction with the accompanying Excel file, which you can download below.

## Download the Files

Enter your email address below to download the comprehensive Excel workbook and PDF.

## Regional Settings

When reading this post keep in mind that my regional settings format dates as dd/mm/yyyy and so the screenshots throughout this post are in this format. However, if you open the accompanying Excel file you may see some dates have switched to match your regional settings, which may be different to mine e.g. mm/dd/yyyy.

Dates and times with a format that begins with an asterisk (*) automatically update based on your PC’s regional settings. You can see an example in the Format Cells dialog box below:

Ok, let's crack on.

# Excel Date and Time 101

## In a nutshell

Excel stores dates and time as a number known as the date serial number, or date-time serial number.

When you look at a date in Excel it's actually a regular number that has been formatted to look like a date. If you change the cell format to 'General' you'll see the underlying date serial number.

The integer portion of the date serial number represents the day, and the decimal portion is the time. Dates start from 1st January 1900 i.e. 1/1/1900 has a date serial number of 1.

**Caution!** Excel dates after 28th February 1900 are actually one day out. Excel behaves as though the date 29th February 1900 existed, which it didn't.

Microsoft intentionally included this bug in Excel so that it would remain compatible with the spreadsheet program that had the majority market share at the time; Lotus 1-2-3.

Lotus 1-2-3 was incorrectly programmed as though 1900 was a leap year. This isn't a problem as long as all your dates are later than 1st March 1900.

## Dates

Excel gives each date a numeric value starting at 1^{st} January 1900. 1^{st} January 1900 has a numeric value of 1, the 2^{nd} January 1900 has a numeric value of 2 and so on. These are called ‘date serial numbers’, and they enable us to do math calculations and use dates in formulas.

The Date Serial Number column displays the Date column values in their date serial number equivalent.

e.g. 1/1/2017 has a date serial number of 42736. i.e. 1^{st} January 2017 is 42,736 days __since__ 31st December 1899.

**Tip**: format the date serial number column as a Date and you'll see they look the same as the Date column values.

## Time

Times also use a serial number format and are represented as decimal fractions.

**Hours:** since 24 hours = 1 day, we can infer that 24 hours has a time serial number of 1, which can be formatted as time to display 24:00 or 12:00 AM or 0:00. Whereas 12 hours or the time 12:00 has a value of 0.50 because it is half of 24 hours or half of a day, and 1 hour is 0.41666' because it's 1/24 of a day.

**Minutes: **since 1 hour is 1/24 of a day, and 1 minute is 1/60 of an hour, we can also say that 1 minute is 1/1440 of a day, or its time serial number is 0.00069444'

**Seconds: **since a second is 1/60 of a minute, which is 1/60 of an hour, which is 1/24 of a day. We can also say one second is 1/86400 of a day or in time serial number form it's 0.0000115740740740741...

## Date & Time Together

Now that we know how dates and times are stored we can put them together - ddddd.tttttt

For example, the date and time of 1^{st} January 2012 10:00:00 AM has a date-time serial value of 40909.4166666667

40909 being the serial value representing the date 1^{st} January 2012, and .4166666667 being the decimal value for the time 10:00 AM and 00 seconds.

More examples below.

# Entering Dates & Times in Excel

## Entering Dates

You can type in various configurations of a date and Excel will automatically recognise it as a date and upon pressing ENTER it will convert it to a date serial number and apply a date format on the cell.

For example, try typing (or even copy and paste) the following dates into an empty cell:

1-1-2009 |

1-1-09 |

1/1/2009 |

1/1/09 |

1-Jan-09 |

1-Jan 09 |

1-Jan-2009 |

1 Jan 09 |

1/1 |

You can see in the table above that entering numbers that look like dates and are separated by a forward slash or hyphen will be recognised as a date. Even typing in a date with the month name gets converted to a date.

However, dates separated with a period like this 1.1.2009, or with spaces between numbers like this 01 01 2009, will end up as text, not a date. Gotta have some limits!

**Tip: **Dates that display ##### in a cell usually indicate that the column is simply not wide enough to display it.

However, if you make the cell really wide and it still displays ##### then this indicates that the date is a negative value and Excel can't display negative dates.

## Entering Dates with Two Digit Years

When you enter a date with two digits for the year e.g. 1/1/09, Excel has to decide if you mean 2009 or 1909.

It goes by the rule that dates with years 29 or before, are treated as 20xx and dates with the year 30 or older are treated as 19xx. See examples below.

**Tip:** You can enter the day and month portions of a date and Excel will insert the year based on your computer's clock. Nice to know for data entry.

## Entering Time

When you enter time you must follow a strict format of at least h:mm. i.e. the hour and minutes are separated by a colon with no spaces either side. Entering the h:mm components will result in a time formatted in military time e.g. 2:00 PM is 14:00 in military time.

If you enter a time that includes a seconds component e.g. 3:15:40, Excel will automatically format the cell in h:mm:ss.

If you want the time to be formatted with AM/PM you can simply enter a space after the time and then type AM or PM, or apply the number format to the cell later. Here are some examples:

## Entering Dates & Time Together

Now that we know how to enter dates and time separately we can put them together to enter a date and time in the same cell.

You can even enter time then date and Excel will fix the order for you.

You'll find that even if you enter AM/PM, that Excel will convert it to military time by default. You can override this with a custom number format. More on that later.

# Simple Date & Time Math

Now that we understand that Excel stores dates and time as serial numbers, you'll see how logical it is to perform math operations on these values. We'll look at some simple examples here and tackle the more complex scenarios later when we look at Date and Time Functions.

## Adding/Subtracting Days from Dates

**Tip:** you can also add/subtract the days directly in the formula e.g. =B10+10 or =B11-5 Although, it's better to place the values you're adjusting by in their own cell or a named range.

## Subtracting Dates from one another

**Tip:** format the cell to General or Number to see the number of days between two dates.

**Note**: the ‘result’ is exclusive of the start day i.e. it assumes the start day is at the end of that day.

## Adding Times to one another

The time being added is input as a time serial number. Notice there are no negative times in the table below. Remember we can't display negative times. Instead we need to use the math operator to tell Excel to subtract time. See examples below.

**Note:** Times that roll over to the next day result in a time-date serial number >= 1. Cell E28 actually contains a time-serial number of 1.08333', but since the cell is formatted to display time formatted as h:mm:ss, only the time portion is visible.

If you want to show the cumulative time (like cell E29) then you need to surround the 'h' part of the time format in square brackets like so: **[h]:mm:ss**

## Subtracting Time from Times

Notice the last result in the table below shows ######, this is because it results in a negative time and Excel can't display that, but notice it can return a negative time serial number. More on how to solve this later.

## Subtracting Times from one another

Again, here the last result shows ###### because it results in a negative time.

# Excel Date and Time Shortcuts

## 'Good to Know' Stuff about Excel Date and Time

- Dates prior to 1st January 1900 are not recognised in Excel.

- A negative date will display in the cell as #######

- Times stored without a date effectively inherit the date 0 Jan 1900 i.e. the month is Jan and the year 1900 and the day is zero. Remember, there are no dates prior to 1/1/1900 from Excel's perspective. This means that times stored without a date e.g. 0.50 for 12:00 PM is the equivalent of 0 Jan 1900 12:00 PM.

This is important because if you try to take 14 hours from 12 hours (without a date) you'll get the dreaded ###### display in the cell, because negative dates and times cannot be displayed. We'll cover workarounds for this later, but for now keep in mind that math on dates and time that result in negative date-time serial numbers cannot be formatted as a date.

### Date Modes

- Excel actually has two date modes. The other mode is called 1904 Date System and is used for compatibility with Excel 2008 for Mac and earlier Mac versions. You can change the date system in the Advanced Options.

In the 1904 date system dates are calculated using 1st January 1904 as the starting point. The difference between the two date systems is 1,462 days. This means that the serial number of a date in the 1900 date system is always 1,462 days greater than the serial number of the same date in the 1904 date system. 1,462 days is equal to four years and one day (including one leap day).

**Caution**; the date setting you choose applies to all dates within the workbook. You can't mix and match modes and you shouldn't reference workbooks that use a different date system in formulas.

Bottom line; don't use the 1904 date system unless absolutely necessary! Click here for more on date systems in Excel.

- Excel applies date number formats based on your system region settings. For example, my system is set to display dates in dd/mm/yyyy format, but if you're in the U.S. your system is likely to format them as mm/dd/yyyy. Excel will automatically convert the format of date serial numbers to suit your system settings as long as it's one of the default date formats and not a custom number format.

## More Excel Date and Time Tips

This post is just the beginning, coming soon:

- Every Excel Date and Time Function explained
- Formatting Date and Time in Excel
- Common Date and Time Calculations

**Tip**: Avoid waiting, download the workbook and get the above topics now.

Enter your email address below to download the comprehensive Excel workbook and PDF.

## Please Share

Working with dates and time are one of the most common struggles for Excel users, so do your friends and colleagues a favour and share this post with them on LinkedIn, Google+, Facebook and Twitter.

Colin Nicholas says

Greetings

I cannot get the formula below to work properly

=IF(“today”>$E$3,$C20,$B20)

can you assist please

thank you

Mynda Treacy says

Hi Colin,

I think you mean:

Mynda

Stefan Johansson says

Thanks, Mynda, for a superb article! It really took out all mystery from using date and time in calculations.

Since you encouraged us to spread word, I posted it on my blog, for my readers to learn about it, as well. The URL is:

https://medieborgaren.se/2017/11/17/2017-11-17-friday/

Mynda Treacy says

Thanks, Stefan! Glad you enjoyed it and thanks for sharing.

Karim Sumar says

Good tips. Couldn’t get the shortcut

CNTRL+Shift +# to work

Perhaps it’s my keyboard!

Mynda Treacy says

Thanks, Karim. Shame the CTRL+SHIFT+# shortcut doesn’t work for you.

julian says

Instead of inserting a comment in a cell, using input message of data validation is a good option. I like it. Besides, what I’m saying is each of your articles including the companion like a textbook with most elaborate content I’ve ever seen. You are really awsome.

Mynda Treacy says

Thanks, Julian! Glad you discovered something new and useful with the data validation ‘comment’.

Mickael Taieb says

Mynda,

Thank you so much for your post. I learn a wealth of information from you and get better at Excel with every single one of your posts.

Which single or combination of formulas are best to use to calculate fiscal years?

Best regards,

Mickael

Mynda Treacy says

Hi Michael,

Thanks for your kind words.

There’s no ‘date’ function for fiscal years i.e. periods that differ to the calendar year, but this post explains your options for converting dates to fiscal years.

Mynda

jim says

On my uk keyboard (where there is a ~/# key next to the enter key) ctrl + # applies date format, ctrl + shift + # (ie ctrl + ~) applies general format

other keyboard layouts are available

Tip: I always show dates as d-mmm-yy, just to make sure nothing american sneaks through (but Access isn’t too keen on this)

Another tip: I get a lot of CSV data where the dates are in dd.mm.yy format; simply select the column, replace “.” with “/” and proper dates appear. Times are more of a challenge, coming as hhmm (with many spaces and 12:mm am shown as just mm). Here I need to designate the column as text on loading (to stop Excel assuming too much), apply the formula =TIME(A1%,MOD(A1,100),) and copy back the values

PowerQuery could help but the layout is annoyingly inconsistent

Slightly off-topic tip: some of our material codes are like 1234E56, which Excel helpfully interprets as 1.234×10^59 – unless it’s an actual numeric value, always download as text!

sorry to over-complicate your simplified introduction

jim

Mynda Treacy says

Thanks for sharing your tips, Jim. The more the merrier 🙂

Thomas H. says

Hi Mynda,

thanks for sharing!

Hint: “However, dates separated with a period like this 1.1.2009, or with spaces between numbers like this 01 01 2009, will end up as text, not a date. Gotta have some limits!” – is not true for “Germany” set as region – then it will be recognised as date as well.

Mynda Treacy says

Lucky you, Thomas. Here in Australia, Excel is more fussy 🙂 Thanks for sharing.