IntermediateLesson 15 of 305 min read

Working with dates and times

Add days and months, find month ends, count working days and calculate ages and service lengths with real, checked date results.

In this lesson you will

  • Build dates with DATE
  • Add days and months and find month ends
  • Count days, years and working days
  • Use TODAY carefully

About 15 minutes

When is it due?

Hamid sends invoices that must be paid thirty days later. He also tracks when staff joined, to give a certificate at five years, and he wants to know how many working days are left before a deadline. Typing dates by hand and counting on a calendar is slow and full of mistakes. Excel stores dates as numbers (lesson 2), which means it can add, subtract and compare them exactly. This lesson shows the functions that make date work easy.

Build dates with DATE

=DATE(year, month, day) builds a real date from three numbers. It is safer than typing, because it never depends on regional settings (day-month versus month-day). To pull the pieces back out, use YEAR, MONTH and DAY.

Real results

With A1 set to 5 March 2026, these results were really calculated and then checked against an independent calendar calculation:

Formula Result
=YEAR / MONTH / DAY of 5 Mar 2026 2026 / 3 / 5
=A1+30 2026-04-04
=EDATE(A1,3) 2026-06-05
=EOMONTH(A1,0) 2026-03-31
=TEXT(A1,“dddd”) Thursday
=WEEKDAY(A1,2) 4
=WORKDAY(A1,10) 2026-03-19
=NETWORKDAYS(2 Nov, 6 Nov 2026) 5
=EDATE(31 Jan 2026,1) 2026-02-28
=A1-DATE(2026,1,1) 63

Several points stand out. Adding 30 to a date adds thirty days, which is why =A1+30 gives the due date of an invoice. EDATE moves by whole months and, as the January 31 example shows, it stops at the end of a shorter month instead of spilling into the next. EOMONTH gives the last day of a month, which is handy for month-end reports. Weekday number 4 with the Monday-start option means Thursday, and TEXT with the pattern dddd gives the name.

Good to know

If the result of a date formula shows as a plain number such as 46086, format the cell as a date (Ctrl+1, Date). The number is correct; only the display needs changing.

Counting days

Subtracting two dates gives the number of days between them. From 15 June 2020 to 5 March 2026 is 2089 days. For years and months, use DATEDIF: =DATEDIF(start,end,“y”) gave 5 complete years, and with “ym” it gave 8 remaining months. That is how you build an age or service length such as ‘5 years 8 months’. DATEDIF still works but is hidden from Excel’s function list, so type it in full. The first date must be earlier than the second.

Working days

NETWORKDAYS(start,end) counts weekdays from Monday to Friday, including both ends. For 2 to 6 November 2026 it gave 5. WORKDAY(start,days) moves forward by a number of working days: ten working days after 5 March 2026 is 2026-03-19. Both accept a list of holidays as an extra argument, so a public holiday does not count as a working day.

Today and now

=TODAY() gives today’s date and recalculates whenever the sheet opens. It is excellent for ‘days remaining’, as in =A1-TODAY(), but it means the answers change every day, so it is not suited to a fixed record such as an invoice date. For a fixed date, type it or press Ctrl+;. These two functions were not run in the test because their answers depend on the day.

Dates and conditions

  • Highlight overdue items: a rule such as =A2<TODAY().
  • Report by month with SUMIFS and a start and end date, as in lesson 12.
  • Group by year or month in a PivotTable, which can do it automatically.
  • Use =TEXT(A1,”mmmm yyyy”) to make month labels, but remember TEXT returns text.

Common traps

  • Dates typed as text (left-aligned) do not calculate. Convert with DATEVALUE or Text to Columns.
  • Mixed regional formats can swap day and month. DATE avoids this.
  • Times are fractions of a day: 0.5 is noon. Format time cells as Time.
  • A date before 1900 is not a date to Excel.
  • Do not subtract in the wrong order, or you will get negative days.

Practice

Make an invoice sheet with invoice dates, terms in days and a due date column using addition. Add a status column that uses TODAY to show ‘Overdue’ when the due date has passed. Then add a staff list with joining dates and use DATEDIF to show service in years and months.

Practice answer

The due date is =A2+B2 (invoice date plus terms). Status can be =IF(C2<TODAY(),“Overdue”,“Open”). If your status is wrong for dates you typed, check that they are right-aligned real dates and not text.

Common mistake

Typing dates as text, like 5.3.26 or March 5th. They look fine but cannot be calculated. Use real dates or the DATE function.

A due-date tracker in three columns

Put the invoice date in A, the payment terms in days in B and the due date in C with =A2+B2. In D show the days left with =C2-TODAY(), and in E a status such as =IF(D2<0,“Overdue”,IF(D2<=7,“Due soon”,”Open”)). Format C as a date and D as a number. Sort by D and the most urgent invoices rise to the top. Add a rule from the next lessons that colours Overdue rows red, and the tracker will warn you without any effort on your part.

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What does =A1+30 give when A1 is a date?

  1. 30 months later
  2. A text label
  3. The date 30 days later
  4. An error

Dates are numbers of days.

Which function counts whole years between two dates?

  1. DATEDIF with “y”
  2. SUM
  3. LEFT
  4. ROUND

DATEDIF(start, end, “y”).

What does NETWORKDAYS count?

  1. All days
  2. Weekdays between two dates
  3. Weekends only
  4. Months

Monday to Friday, ends included.

Why is =TODAY() not suitable for an invoice date?

  1. It is too slow
  2. It needs a macro
  3. It returns text
  4. It changes every day

Type a fixed date for records.

What does EDATE(A1,3) do?

  1. Adds three days
  2. Moves a date by three months
  3. Finds the weekday
  4. Formats a date

It also stops at a shorter month’s end.

Summary

  • Dates are numbers, so you can add, subtract and compare them.
  • DATE, EDATE, EOMONTH, DATEDIF, NETWORKDAYS and WORKDAY cover most needs.
  • TODAY changes daily; type fixed dates for records.
  • Text dates cannot be calculated.

Next lesson

Next: data validation and drop-down lists.

Questions

Why does my date formula show a number?

The cell is formatted as General or Number. Change it to a Date format; the value is fine.

Does DATEDIF work in Google Sheets?

Yes, Google Sheets supports DATEDIF too, with the same unit codes.

How do I count working days with holidays?

List the holidays in cells and add the range as the last argument, as in NETWORKDAYS(start,end,holidays).

Progress is saved in this browser.