How to Work With Dates in Excel Formulas

Last updated on 2024-05-15.

How Excel Regards Dates

This article might help understanding what it means to work with dates if you're not familiar with how Excel applies its internal calendar.

The date you input into a cell on your spreadsheet may be displayed in any one of a number of formats, including

Internally, Excel treats that date as a particular whole number. The format applied to the cell containing that number determines how it appears to you on screen.

To demonstrate this point, use the keyboard shortcut Ctrl+; to input into an empty cell. Excel will assume your input to be today's date — . It will display your input according to the format applied to that cell.

Now apply the General number format to the cell and displayed will be the number Excel regards as representing today's date: .

Now to understand why Excel thinks of as .

Excel's Internal Calendar

Excel works to its internal calendar, which starts on 00 January 1900 [sic] and increments by one for each day since then. Hence the number one represents 01 January 1900.

To test this out:

• Input into an Excel cell the number one. Excel will display this as the numeral 1, under its default General number Format
• Change the number format of the cell to Long Date.
• Note that Excel now displays a date, as something like 01 January 1900. What Excel has done is count one day on from 00 January 1900.

Now try this:

• Into another cell input the number 44439
• Note that if the number Format applied to that cell is General, then the number is displayed as such. If the Format is Long Date, then the number is displayed in a format indicative of the date 31 August 2021.

What you've demonstrated is that Excel considers dates to be whole numbers. It's the format that makes these numbers appear as dates.

Thus, subtracting 01 January 1900 from 31 August 2021 is in effect subtracting one from 44439. The result yields 44438 — the number of days separating the two dates.

Limits to Excel's Calendar

The calendar sets limits to dates that Excel will recognise as such:

• Calculations not possible on dates earlier than January 1900. Don't expect Excel to think of a negative number as a date
• Calendar extends only as far into the future as 31 December 9999, the last time I checked.

Your Computer's Region Settings

Excel's default format for input of dates, is determined by the region to which the computer you're using is set. The setting is in Window 10's Control Panel: open Control Panel and select Clock and Region —

Then choose Region to display the Region dialog box —

The settings shown above are for my computer, as supplied to me in the UK. I don't alter these defaults so that my settings are in tune with those of the majority, I assume, of PCs in the UK.

I never insist that a client adopts one set of Language Preferences over another. I believe I must make a spreadsheet manage dates correctly irrespective of any tinkering a particular user may do to their Windows settings. (I once witnessed the havoc played by a joker in one office who changed the preferences for the date formats on a colleague's PC.

Simple Calculations Involving Dates

Since to Excel a date is a number, performing calculations involving dates is a matter of applying arithmetic. In this example one date is subtracted from a second one to determine the number of days between them:

Adding a number to a date yields a date that number of days ahead:

Give careful thought to the construction of a formula. Note the addition of one to the date difference in this formula. That one makes the result inclusive of the day of the start of the project:

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —