How are dates stored in Excel?

TrustPilot

starstarstarstarstar Excellent

Forum home » Topics » How are dates stored in Excel?

How are dates stored in Excel?

Have you ever wondered how dates are actually stored in Excel?

As many people need to use dates in formulas, Excel stores the dates as numbers. For example, this allows dates in two columns to be subtracted.

This does mean you have to consider date formats when entering dates into cells. Excel recognises separators such as "/" or "-" but does not recognise dot separators. However, Excel can display dates with dot separators.

To see the stored number in a cell rather than the date, select Format Cells, Number and then General from the Category column. For example, the date 1/1/12 displays 40909.

This is the number of days since the earliest allowed date. Try typing 1 into a different cell and formatting the cell as a date. You will see that the earliest date that can be stored is 1/1/1900.

So what is the largest date that Excel can store?
31/12/9999!

Related forum posts:

Converting dates

I have a range of dates that I have exported into Excel from another system. I need to compare these dates with another to get a period of time. However, Excel is reading the exported dates as text, not as dates. I have tried doing Format - Cells etc. which has no effect. Can anyone think of a way to convert these to dates or even just to numbers? Thanks Tristan

Read forum post

 

Converting Date Format

My question sounds quite silly- I

Read forum post

 

Related articles

Using Dates Within Excel

Read article

 

How Are Dates Stored In Excel?

Have you ever wondered how a date is actually stored in Excel? This article describes how Excel stores dates and covers an example of how this can be used in calculations.

Read article

 

 

Live dates & prices

 

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


TrustPilot
TrustScore 4.9 / 5       1108 reviews