What You Should Know About Dates in Excel

What’s up with dates and times in Excel? What is a serial number? And what does the date January 1st, 1900 have to do with it? Today, we’re de-mystifying dates in Excel. Let’s do this.

Dates in Excel: Serial numbers

In the video below, you can see what happens when you change the number format of a date to General.

What happens when you change a date to a General number format. You can see the underlying serial number that Excel uses behind the scenes.

Weird, isn’t it? What you see here is the underlying number that Excel uses to represent the date. This number is called a serial number. But what does it mean? And why does 4/10/2020 become 43931?

Serial numbers explained

Let us explain: 43931 is the number of days that have passed since 1/1/1900 to the date 4/10/2020. That’s how Excel saves dates.

Knowing this helps us understand some of Excel’s behavior. For example, we can predict what will happen when you calculate 4/10/2020 + 1 in Excel. The result will be 43931 + 1 = 43932. And formatted as a date, you get 4/11/2020, one day later. In the same vein, 4/10/2020 - 7 will result in a serial number that is 7 days before 4/10/2020.

So now you can use the TODAY function to calculate yesterday’s date! It’s as simple as:

=TODAY() - 1

Note on dates before 1900 – You might be wondering what happens with dates before 1/1/1900. You can try this for yourself: in a cell in Excel, type 1/1/1900. You will see the number format change to Date automatically. If you then type 12/31/1899 (just one day earlier) into another cell, the number format won’t change. Excel does not recognize it as a date because it doesn’t have a serial number to use for it. The conclusion is that Excel just isn’t very useful for working with dates before the year 1900.

This is not too difficult, right? It’s just Excel’s way of representing dates. However, it becomes more complicated when we add time as well.

Times

For dates with times, a fraction is added to the serial number to represent the time of the day. For example, 43931.00 is the serial number for the date-time combination of 4/10/2020 12:00 AM.

The fraction is the percentage of the day that has passed at that time. So at 12 AM it’s .0000 and at 12PM it’s .5000. 1 AM is at the fraction 1/24 (.041667).

Overview

Here’s an overview for some different times of the day and their corresponding fractions and decimals.

TimeFractionDecimal
12 AM0.00000
1 AM1/24.04167
2 AM2/24.08333
12 PM12/24.50000
12:01 AM1/24/60.000694
12:01:01 AM1/24/60/60.000011574

You can use these fractions either on their own to only represent a time (0.5 for 12 PM) or combined with a whole number (43931.5 for 10/4/2020 12 PM) to represent both date and time.

So by combining the whole number for the date and a fraction for the time, we can represent any date-time combination from 1900 up until now. Pretty cool, huh?

Getting the current date and current time

Excel has functions that return the current date and time so you can use them in your worksheets. This can be handy for calculating ages based on date of birth. Or calculating how many days are left before a deadline.

For the current date, you can use the TODAY function. We have a page about the Today function here.

For the current date and time, there is the NOW function. We have a page about Now as well, with examples and explanations.

Concluding

We hope you understand the inner workings of Excel a little better after reading this. If you have any more questions about date-shenanigans in Excel, let us know in the comments.

Have a great day!


You may also like

Is there a ‘minus sum’ for spreadsheets?

7 Excel Tricks for Marketers

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"36c04":{"name":"Main Accent","parent":-1},"3a8fd":{"name":"Accent Light","parent":"36c04","lock":{"saturation":1,"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]},"original":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]}}]}__CONFIG_colors_palette__

​Get Good at Excel on Autopilot

Sign up to our newsletter and receive ​Excel articles, tips and tricks delivered straight to your inbox. All you have to do is read them! ​Plus, to get you started, you'll receive a PDF with 200+ Excel shortcuts.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"2dd0d":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"2dd0d":{"val":"rgb(45, 164, 92)","hsl":{"h":143,"s":0.57,"l":0.41}}},"gradients":[]},"original":{"colors":{"2dd0d":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Join List
>