Dates in Excel: TODAY, EDATE, NETWORKDAYS and Ageing

Excel for Jobs: Formulas, Pivot Tables and Office Skills · Lookups, Text and Dates · 4 min read · Updated

Dates are just numbers

  1. Excel stores a date as a number of days, so you can calculate with it.
  2. Add days, add months with EDATE, or subtract two dates to get the days between them.
  3. NETWORKDAYS and WORKDAY skip weekends and holidays when you need working days.
  4. You get due dates, ages and overdue days, which you format as dates or numbers.

Dates are everywhere in office work: invoice due dates, attendance, leave, joining dates, contract ends, delivery times. Excel is very good at date maths, as long as you remember one thing from lesson 2: a date is a number. Once that clicks, date calculations are just ordinary arithmetic.

Dates are numbers

Excel counts days. 1 January 1900 is day 1, and each following day is the next whole number. Excel shows the number as a date through the format. So if A2 holds 1-Jun-2024 and B2 holds 15-Jun-2024, then =B2-A2 gives 14, the number of days between them. Format the answer cell as General or Number. Excel sometimes formats it as a date, and then you see a strange date instead of 14.

Add days the same way: =A2+30 is thirty days after A2. This is the simplest way to calculate a payment due date for “30 days credit”.

TODAY and NOW

  • =TODAY() returns today's date. It changes every day when the file opens or recalculates.
  • =NOW() returns the date and the time.
  • For a date that must not change, such as the date an invoice was written, type it with Ctrl+; instead of a formula.

A due-date warning: if the due date is in column C, =IF(C2<TODAY(), "Overdue", "OK") marks late invoices. Days overdue: =MAX(0, TODAY()-C2).

Build a date from parts, and take parts out

=DATE(2024, 6, 15)     -> 15-Jun-2024
=YEAR(A2)             -> 2024
=MONTH(A2)            -> 6
=DAY(A2)              -> 1

DATE(year, month, day) is the safe way to create a date from separate pieces, for example when a sheet holds the year, month and day in three columns. It even handles overflow: month 13 becomes January of the next year.

EDATE and EOMONTH: months

=EDATE(A2, 3)      -> the same day, 3 months later
=EDATE(A2, -1)     -> one month earlier
=EOMONTH(A2, 0)    -> the last day of the month of A2
=EOMONTH(A2, 1)    -> the last day of the next month

Use EDATE for subscriptions, loan instalments and contract renewals. Use EOMONTH for month-end reports and for payment terms such as “end of the month”. If you see a number such as 45473 instead of a date, you only need to format the cell as a date.

Working days: NETWORKDAYS and WORKDAY

  • NETWORKDAYS(start, end) counts the working days between two dates, Monday to Friday, both included.
  • NETWORKDAYS(start, end, holidays) also skips a list of holiday dates.
  • WORKDAY(start, days, holidays) gives the date after that many working days.

For example, an order is received on Monday 3 June 2024 and takes 5 working days. =WORKDAY(DATE(2024,6,3), 5) gives Monday 10 June, assuming no holidays. Some countries have a Sunday-only weekend or different weekend days. Use NETWORKDAYS.INTL and WORKDAY.INTL, where you choose which days are the weekend. Keep your public holidays on a separate list and name that range.

Age and length of service: DATEDIF

DATEDIF(start, end, unit) returns whole years, months or days between two dates. It is an older function that Excel does not list in its help, but it works.

=DATEDIF(B2, TODAY(), "Y")     -> complete years (age or service)
=DATEDIF(B2, TODAY(), "M")     -> complete months
=DATEDIF(B2, TODAY(), "YM")    -> months left over after full years

A staff list with joining dates can show “3 years, 4 months” by joining the pieces: =DATEDIF(B2,TODAY(),"Y")&" years, "&DATEDIF(B2,TODAY(),"YM")&" months". The simple (TODAY()-B2)/365 is not exact, because of leap years. Use YEARFRAC or DATEDIF when exactness matters.

Weekday and week number

=WEEKDAY(A2, 2)       -> 1 for Monday to 7 for Sunday
=TEXT(A2, "dddd")      -> the day name, such as Saturday
=TEXT(A2, "mmmm yyyy") -> June 2024
=WEEKNUM(A2)           -> the week of the year

TEXT turns a date into text in the style you choose, which is handy for headings and for grouping by month. The format codes follow your language settings in some countries, so test them.

Time

Times are fractions of a day: 12:00 is 0.5. To find hours worked, subtract the start time from the end time, and format the result as [h]:mm. Square brackets let the total go above 24 hours when you add up a week. To get hours as a plain number, multiply by 24: =(C2-B2)*24. For shifts that end after midnight, add one day if the end is smaller: =IF(C2<B2, C2+1, C2)-B2.

Typical date problems

  • Text dates that will not calculate. Check alignment, and convert them (lesson 2).
  • Day and month swapped after importing data from a system with a different regional setting.
  • Two-digit years such as 24, which Excel may read as a different century in some old files.
  • A date shows as a number. Format as a date.
  • A result shows as a date when you wanted days. Format as General.

Your task

  1. Make a small invoice list with invoice dates and 30-day terms. Calculate the due date, and mark invoices that are overdue today.
  2. Add a staff list with joining dates and calculate complete years of service with DATEDIF.
  3. Find the number of working days between 3 June 2024 and 28 June 2024 with NETWORKDAYS. You should get 20.
  4. Use EOMONTH to show the month end of each invoice date.

How to check it worked

  • Due date = invoice date + 30, formatted as a date.
  • NETWORKDAYS for 3 to 28 June 2024 returns 20.
  • You can explain why TODAY() changes every day and when you should avoid it.

Frequently asked questions

Why does my date subtraction show a date instead of days?

Excel formatted the result as a date. Change the cell format to General or Number.

How do I count working days excluding holidays?

Use NETWORKDAYS(start, end, holidays) with a list of holiday dates. For different weekend days use NETWORKDAYS.INTL.