Dates in Excel: how they work and the formulas you need

Dates are everywhere in spreadsheets: invoice dates, due dates, start dates, birth dates, the week a sale was made. They are also where many people's trust in Excel breaks down. A date turns into a five-digit number for no visible reason, a lookup on a date fails, a total for October includes a few days of November, or a colleague in another country opens the file and every date looks wrong.

Nearly all of it comes down to one fact, and once you understand it, date formulas become simple arithmetic. This guide explains that fact, then works through the date tasks that come up again and again at work: days between dates, adding days and months, month-ends, working days, ages, and dates that arrive as text.

A date is a number

Excel stores every date as a whole number: the count of days since Jan 1, 1900, which is day 1. Jan 2, 1900 is day 2, and Mar 15, 2024 is day 45,366. What you see in the cell, "Mar 15, 2024" or "3/15/2024", is only a number format laid on top of that number.

This one fact explains most of the strange things dates do:

• A date turns into a number when it loses its format, for example when you paste values into a cell formatted as General, or when a formula returns a date into an unformatted cell. Nothing is broken. Format the cell as a date with Ctrl+1 and it reads as a date again.
• You can do arithmetic on dates. Subtracting one date from another gives the days between them, and adding 30 gives the date 30 days later. Most date formulas are just that.
• Comparisons work. A later date is a bigger number, so =B2>C2 tells you whether B2 is after C2, and you can sort, filter, MIN and MAX dates like any other numbers.

Times work the same way, as fractions of a day: 0.5 is noon and 0.75 is 6 PM. A cell holding 45366.5 is noon on Mar 15, 2024. That is why the difference between two times, multiplied by 24, gives hours: =(C2-B2)*24.

Google Sheets counts from Dec 30, 1899 instead, so its day numbers for very old dates differ by a day or two, but every date since Mar 1, 1900 has the same number in both programs. You can move files between them without the dates shifting.

How dates are shown, and why they look different abroad

Because the date is a number and the look is a format, the same file can show the same date in different ways. A US version of Excel shows 3/15/2024, a Danish one 15-03-2024, and a long format shows Friday, March 15, 2024. The value underneath is 45,366 in all three.

The trouble starts when dates are typed or imported as text. Excel reads what you type using your computer's regional settings. In the US, 03/04/2024 is March 4; in most of Europe, it is April 3. If you type a date that doesn't fit your settings, such as 15/03/2024 on a US computer, Excel can't read it as a date and keeps it as text. It looks like a date, but it is a string of characters, and no date formula works on it.

Two habits avoid most of this:

• Type dates in a form that can't be misread, such as 2024-03-15, which Excel accepts in every locale, or build them with the DATE function.
• Format for the reader, not for the data. Use whatever format your audience expects, and remember it changes nothing about the value.

Days between two dates

Subtract the earlier date from the later one:

If the result shows as a date, such as Feb 4, 1900, Excel copied the date format from the cells it referenced. Format the result cell as a number and it shows 35.

Whether you need to add 1 depends on the question. From Sep 14 to Oct 19 is 35 days between the two dates. If you are counting the days of a hotel booking or a project where both the first and the last day count, it is 36 days, so write =C2-B2+1. Decide which one you mean before you write the formula, because both are "right".

Practice it in days between two dates. Excel also has a DAYS function, =DAYS(C2,B2), which does the same subtraction with the end date first. It is mostly there for readability.

Adding days: due dates and deadlines

Since a date is a number of days, a date plus a number is a later date:

This is how due dates, follow-up dates and expiry dates are usually calculated. Keep the number of days in its own cell, as here, rather than inside the formula. When the payment terms change from 30 to 45 days, you change one cell, and anyone reading the sheet can see what the formula assumes. The due date exercise drills it.

Subtracting a number works too: =B2-7 is a week before B2.

Building a date with DATE

DATE takes a year, a month and a day and returns the date: =DATE(2024,3,15). Use it whenever you need a fixed date in a formula, because typing a date into a formula as text, such as "3/15/2024", depends on the computer's regional settings and can be read differently on another machine.

DATE has a useful property: it rolls over. A month of 13 is January of the next year, and a day of 0 is the last day of the previous month. So =DATE(2024,14,1) is Feb 1, 2025, and =DATE(2024,3,0) is Feb 29, 2024. That makes it easy to build dates relative to another date, such as the first of next month: =DATE(YEAR(B2),MONTH(B2)+1,1). The DATE exercise starts with the basic form.

Taking a date apart

The reverse of DATE is a family of small functions that each return one part of a date:

• YEAR, MONTH and DAY return the year, the month as 1 to 12, and the day of the month: =MONTH(B2) is 10 for any date in October. Practice it in getting the month.
• WEEKDAY returns the day of the week as a number. By default Sunday is 1, which surprises people outside the US; =WEEKDAY(B2,2) makes Monday 1 and Sunday 7, which is easier to test for weekends: =WEEKDAY(B2,2)>5. The day of the week exercise uses it.
• TEXT returns the date as text in any format, which is handy for labels and headings: =TEXT(B2,"mmm yyyy") gives Oct 2026, and =TEXT(B2,"dddd") gives Thursday. The result is text, so use it for display, not for further date arithmetic.

These are what you use to group dates: a helper column with =MONTH(B2) or =TEXT(B2,"yyyy-mm") lets you total by month with SUMIF or a pivot table.

Months: EDATE and EOMONTH

Adding 30 days is not the same as adding a month. Months have 28 to 31 days, so contract dates, subscription renewals and payment schedules need a function that knows the calendar.

EDATE moves a date a number of months forward, or back with a negative number. =EDATE(B2,12) is the same date a year later. When the target month is shorter, EDATE stops at the month's last day:

That is usually what contracts mean, but check it when the rules matter: a monthly charge on the 31st falls on the 28th, 29th or 30th in shorter months. Practice it in contract end date with EDATE.

EOMONTH returns the last day of a month: 0 for the same month, 1 for the next, -1 for the previous. It is the workhorse of finance and accounting, where periods end on the last day of the month:

Two patterns worth knowing by heart:

• The first day of a month: =EOMONTH(B2,-1)+1 is the first day of B2's month, and =EOMONTH(B2,0)+1 is the first day of the next month. The first day of next month exercise drills the second one.
• The number of days in a month: =DAY(EOMONTH(B2,0)) returns 28, 29, 30 or 31.

Practice EOMONTH on its own in last day of the month.

Working days: NETWORKDAYS and WORKDAY

Business deadlines are usually counted in working days, not calendar days. Excel has two functions for that, and both skip Saturdays and Sundays.

NETWORKDAYS counts the working days between two dates, including both the start and the end date. Its optional third argument is a list of holidays to skip as well:

Keep the holidays in a range on the sheet, ideally on a sheet of their own, and point every formula at it with $ signs. Each year you add the new dates in one place. Practice it in counting working days and then with holidays around Easter.

WORKDAY goes the other way. It takes a start date and a number of working days and returns the date that many working days later:

WORKDAY does not count the start date itself, so one working day after a Friday is the following Monday. It takes the same optional holiday list as NETWORKDAYS. The delivery date exercise asks for it.

If your weekend is not Saturday and Sunday, NETWORKDAYS.INTL and WORKDAY.INTL take an extra argument for which days are off, such as Friday and Saturday, or Sunday only.

Ages and length of service: DATEDIF

"How old is she?" and "how many years has he worked here?" sound like subtraction, but they are not. Subtracting the years with =YEAR(C2)-YEAR(B2) is wrong for anyone whose birthday hasn't come yet this year, and dividing the days by 365 drifts with leap years.

DATEDIF counts complete years, months or days between two dates:

The third argument picks the unit: "y" for complete years, "m" for complete months and "d" for days. "ym" gives the months left over after the complete years, which is how you write "36 years and 5 months": =DATEDIF(B2,C2,"y")&" years and "&DATEDIF(B2,C2,"ym")&" months".

DATEDIF is an odd function. It is not in Excel's function list or its formula suggestions, because Microsoft kept it only for compatibility with older spreadsheet programs, but it works in every version of Excel and in Google Sheets. The start date must come first; the other way round gives #NUM!. Practice it in working out an age and months of service.

TODAY, and why tests avoid it

=TODAY() returns today's date and updates every time the workbook recalculates. It is the natural second date for "days until the deadline", =B2-TODAY(), or "age today", =DATEDIF(B2,TODAY(),"y").

Because the result changes every day, TODAY is a poor fit for anything that needs to stay put. A report that says "overdue by 12 days" says 13 tomorrow. If a figure must be frozen, such as the age at the date of hire, use a fixed date instead. NOW works like TODAY but includes the current time.

Tests and exercises almost always give you a fixed "today" in a cell, so your answer can be checked. When a task says "as of the date in E1", use E1, not TODAY.

TODAY is at its best in conditional formatting, where =$B2<TODAY() colors every deadline that has passed and the colors move on by themselves each morning. The conditional formatting guide has the rules for overdue dates, deadlines coming up and weekends.

Dates that arrive as text

Data exported from other systems, downloaded from a website or pasted from an email often contains dates that look right but are text. They cause some of the most confusing errors in Excel: sorting puts April before February, a lookup returns #N/A, a SUMIFS for October returns 0, and subtracting two of them gives #VALUE!.

Three quick ways to tell:

• Alignment. Unless someone changed it, numbers and real dates sit at the right of the cell and text sits at the left.
• ISNUMBER. =ISNUMBER(A2) returns TRUE for a real date and FALSE for text.
• Changing the format. Format the cell as a number. A real date turns into a five-digit number; text stays exactly as it was.

How you fix it depends on the text:

• If the text matches your regional settings, =DATEVALUE(A2) converts it. Selecting the column and using Data > Text to Columns, and clicking Finish, often converts a whole column in place.
• If the order is different, for example day-month-year text on a US computer, or ISO dates with dashes, take the parts out with LEFT, MID and RIGHT and rebuild the date with DATE:

LEFT, MID and RIGHT return text, but DATE accepts text that looks like a number, so the formula needs no further conversion. Once the helper column holds real dates, copy it and paste it back as values over the original column, with Paste Special. Our guide on cleaning data covers the text functions in more detail.

Totals by month

A question you will get in almost every job that uses Excel: what was the total for October? With real dates in the data, it is SUMIFS with two conditions on the same column, one for the start of the month and one for the end:

The conditions are built by joining an operator to a cell with &: ">="&E1 means on or after the date in E1. EOMONTH gives the last day of the month, so the formula works for any month you type into E1. Because the condition uses <= the last day, a date at 3 PM on Oct 31 would be left out. When the data includes times, use "<"&EOMONTH(E1,0)+1, before the first day of the next month, instead.

The same two conditions work in COUNTIFS and AVERAGEIFS. For a full table of totals per month, a pivot table that groups the dates by month is often quicker; our pivot table guide shows how.

Mistakes that cost the most

• Adding 30 days for a month. Use EDATE for months, and EOMONTH for month-ends.
• Typing dates into formulas as text. "10/29/2026" is read differently on a computer with other regional settings. Use DATE or a cell reference.
• Subtracting years for an age. YEAR(C2)-YEAR(B2) is a year too high for everyone whose birthday hasn't come yet this year. Use DATEDIF.
• Dates stored as text. If a date formula gives #VALUE! or a total by month gives 0, check with ISNUMBER first.
• Month totals that cut off the last day. Times after midnight on the last day fall outside <= the month-end. Compare with the first day of the next month instead.
• Forgetting holidays. NETWORKDAYS without the holiday list overcounts every month with a public holiday in it.
• Grouping by TEXT and then sorting. =TEXT(B2,"mmm") sorts alphabetically, so April comes first. Use "yyyy-mm" or the month number for anything that needs to sort.

Dates in Excel tests

Date questions appear in most employer Excel tests, and they are especially common in admin, HR, finance and accounting roles. Typical tasks:

• "How many days did each order take to deliver?" One subtraction, formatted as a number.
• "Calculate the due date, 30 days after the invoice date." A date plus a number, formatted as a date.
• "Find the last day of the month for each invoice." EOMONTH with 0.
• "How many working days are there between the two dates?" NETWORKDAYS, with the holiday range if the task gives one.
• "Calculate each employee's age, or years of service, as of the date in E1." DATEDIF with "y", and E1 rather than TODAY.

The format of the answer cell matters in these questions. If the expected answer is a number of days and your cell shows a date, the marker or the automatic check may count it as wrong, even when the value is right. Check the format before you move on. Our job interview guide has more on working against the clock.

Summary

Excel stores a date as a number of days and shows it through a format. That is why you can subtract dates to get days, add numbers to get later dates, and compare, sort and sum them like any other numbers. For months, use EDATE and EOMONTH instead of adding 30. For business days, use NETWORKDAYS and WORKDAY with a holiday list. For ages and service, use DATEDIF. When a date refuses to work, check whether it is text, and rebuild it with DATE.

The exercises below follow the same order, from the first subtraction to working days and ages, each in a live sheet with real dates.