Excel Help

Dates in Excel: Count Days, Add Weeks and Never Count on Your Fingers Again

How many days until the deadline? When is the invoice due? How old is this record? Excel can answer all of these in a blink, once you know its one big date secret.

About 10 minutes to read

Have You Ever Counted Days on a Calendar?

"The invoice is due 30 days after the invoice date." "The project runs from 15 January to 20 February. How many days is that?" Most of us have, at some point, reached for a calendar and started counting.

Excel can do this for you, and it is surprisingly easy. All you need to know is one secret about how Excel thinks about dates.

In one sentence: Excel stores every date as a plain number, so you can add, subtract and compare dates just like any other numbers.

The Big Secret: Dates Are Numbers

Excel counts days. Day number 1 is 1 January 1900, and every day after that adds one. The date you see on the screen is just a nicely dressed-up number.

	A	B
1	What you see	What Excel stores
2	01/15/2025	45672
3	02/20/2025	45708

The difference between the two numbers is 45708 − 45672 = 36. That is exactly the number of days between 15 January and 20 February.

That is the whole trick. Because dates are numbers, subtracting one date from another gives the number of days, and adding a number to a date gives a later date. No special tools needed.

Note on the date format: the examples in this article use the month/day/year order common in the United States (01/15/2025 is 15 January). Your Excel may show dates in a different order. We explain this further down.

Today's Date, Always Up to Date

To show today's date, type this in a cell:

=TODAY()

There is nothing between the brackets. Every time you open the file, the cell shows the current date, so you never have to retype it. If you also want the time, use =NOW().

Good to know: because it changes every day, =TODAY() is perfect for "days left" and "days overdue" calculations, but not for a date you want to keep fixed, like an invoice date. For those, type the date yourself (or press Ctrl + ; to insert today's date as a fixed value).

Formula 1: How Many Days Between Two Dates?

	A	B	C
1	Start	End	Days
2	01/15/2025	02/20/2025	36

=B2-A2

Just end minus start. The answer is 36 days.

If the answer looks like a date (for example 02/05/1900) instead of 36, the cell has a date format. Select the cell and change the format to General or Number on the Home tab.

This formula does not count the start day itself. If you want to count both the first and the last day, add 1: =B2-A2+1.

Formula 2: What Date Is 30 Days from Now?

	A	B	C
1	Invoice date	Days to pay	Due date
2	01/15/2025	30	02/14/2025

=A2+B2

Date plus a number of days gives a new date. Excel handles months of different lengths and leap years by itself, so you never have to check how many days February has.

To go backwards in time, subtract instead: =A2-30 gives the date 30 days earlier.

If the result shows as a number such as 45702, the cell needs a date format. Select it and choose Short Date on the Home tab.

Formula 3: Working Days Only

Weekends do not count when you are counting working days. Excel has two helpful functions for this.

	A	B	C	D
1	Start	End	Working days	10 working days after start
2	01/15/2025	02/20/2025	27	01/29/2025

Count the working days between two dates

=NETWORKDAYS( A2, B2 )

There are 36 days between our dates, but only 27 are working days (Monday to Friday). Note that NETWORKDAYS counts both the start day and the end day.

Find the date after a number of working days

=WORKDAY( A2, 10 )

This gives the date that is 10 working days after the start date: 29 January. It skips Saturdays and Sundays automatically, which is perfect for delivery times and deadlines.

What about holidays? Both functions accept an extra piece at the end: a list of holiday dates to skip as well. For example, =NETWORKDAYS(A2, B2, F2:F10) where F2:F10 holds your holidays.

Formula 4: Pull Out the Year, Month or Day

Sometimes you only need one part of a date, for example to group sales by month.

	A	B	C	D
1	Date	Year	Month	Day
2	03/25/2025	2025	3	25

The three formulas are as simple as they look:

=YEAR(A2) gives 2025
=MONTH(A2) gives 3
=DAY(A2) gives 25

And the other way around, DATE builds a date from three separate numbers:

=DATE(2025, 3, 25)

The order is always year, month, day, no matter how your Excel shows dates. This makes DATE a very safe way to write a date inside a formula.

Why Does My Date Look Different? (Date Formats)

The same date can be written in different ways around the world, and Excel follows the settings of your computer:

Order	Example	Common in
Month / Day / Year	01/15/2025	United States
Day / Month / Year	15/01/2025	UK, most of Europe, many other countries
Year-Month-Day	2025-01-15	International standard (ISO)

Here is the tricky part: 03/04/2025 means March 4 in the US, but 3 April almost everywhere else. If both numbers are 12 or less, nobody can tell which is which without knowing the format.

The date is the same, only the look differs. Behind the scenes it is always the same number. You can change how it is displayed without changing the date: select the cell, press Ctrl + 1, choose Date and pick the style you like.

When you type a date, Excel reads it according to your regional settings. Typing 25/03/2025 in a US-style Excel often gives text instead of a date, because there is no 25th month. If your date sits on the left side of the cell and will not calculate, this is usually the reason (see "Common Problems" below).

Comma or Semicolon? It Depends on Where You Live

Some of the functions above, such as NETWORKDAYS and DATE, have several pieces. If a formula from the internet gives an error even though it looks perfect, the separator is often the reason.

Style	Separator	Typical regions	Example
American	Comma ,	USA, UK, Canada, Australia	`=DATE(2025, 3, 25)`
European	Semicolon ;	Finland, Sweden, Germany, France, Spain	`=DATE(2025; 3; 25)`

The formula does the same thing in both styles. If you get an error, swap every comma for a semicolon, or the other way around. Simple formulas like =B2-A2 have no separators and work everywhere. The examples in this article use commas.

Advanced

Exact Age, Months and the End of the Month

Years and months between two dates

"How old is this person?" is harder than it sounds, because years have different lengths. Dividing days by 365 gives wrong answers around birthdays. Excel has a handy (and slightly hidden) function for this called DATEDIF.

	A	B	C	D
1	Born	As of	Full years	Extra months
2	06/15/1990	01/15/2025	34	7

=DATEDIF(A2, B2, "y")

The last piece says what to count: "y" for complete years, "m" for complete months and "ym" for the months left over after the full years. So =DATEDIF(A2, B2, "ym") gives 7. The person is 34 years and 7 months old. For the current age, use TODAY() in place of the second date.

Don't be surprised: DATEDIF will not show up in Excel's suggestions when you type it. It works anyway. Just type it in full.

Add months instead of days

Adding 30 is not the same as adding a month. For "one month later" or "in 3 months", use EDATE:

=EDATE(A2, 3)

With 01/15/2025 in A2, this gives 04/15/2025. EDATE is also clever at month ends: one month after 01/31/2025 is 02/28/2025, because February has no 31st.

The last day of the month

EOMONTH gives the last day of a month. The second piece says how many months to move: 0 for this month, 1 for next month, −1 for last month.

=EOMONTH(A2, 0)

With 01/15/2025 in A2, the answer is 01/31/2025. Handy for month-end reports and for payment terms like "end of month".

Common Problems and How to Fix Them

The date is aligned to the left and will not calculate

Excel thinks it is text, not a date. Real dates are right-aligned by default. This often happens when a date was typed in a format that does not match your regional settings, or when data was copied from another system. Retype the date in your own format, or use Data > Text to Columns and choose the date order to convert a whole column at once.

You see 45672 instead of a date

The cell has a number format. Select it and choose Short Date on the Home tab. The number is fine, it just needs a date outfit.

You see a date instead of a number of days

The opposite problem: the result of end minus start got a date format. Set the cell format to General or Number.

You see #VALUE!

One of your "dates" is probably text. Check that both cells are right-aligned and contain real dates. A cell with an extra space or a typo in the month looks like a date but is not one.

You see ##### in the cell

The column is too narrow to show the date. Double-click the border on the right side of the column letter to widen it. A negative date result also shows hashes, which means you subtracted in the wrong order (the start date is later than the end date).

The day and month are swapped

This happens when a file made in a US-style Excel is opened in a European one, or the other way around. If the day is 12 or less, Excel may silently read 03/04/2025 the wrong way. To be safe, build dates with DATE(year, month, day) or use the unambiguous 2025-03-04 format.

Excel says there is a problem with your formula

Check the separators. If your Excel uses European settings, use semicolons (;) instead of commas (,), as explained above.

The year shows as 1900

The cell holds a very small number, such as 1 or 5, formatted as a date. Excel is showing the first days of 1900. Change the cell to a normal number format.

The Short Version

Excel stores dates as numbers, so you can add and subtract them.
Days between two dates: end minus start (=B2-A2).
A date some days later: =A2+30.
Working days: NETWORKDAYS to count them, WORKDAY to find a date.
YEAR, MONTH and DAY pull out parts of a date. DATE builds one from year, month and day.
TODAY() always shows the current date.
Use DATEDIF for exact age, EDATE for months and EOMONTH for month ends.
If a result looks wrong, check the cell format. A date can look like a number and the other way around.