Excel Date and Time Functions: TODAY, DATEDIF, EOMONTH, NETWORKDAYS

Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021, Excel 2019 and Excel 2016

Due dates, payment terms, staff attendance, delivery schedules and age calculations all depend on dates. Excel handles them very well once you understand one simple idea: every date is really a number. This guide explains how Excel stores dates and times, then walks through the functions you will use most often for invoices, HR records and project plans.

How Excel stores dates and times

Excel counts days from 1 January 1900, which is day 1. So 1 January 2026 is stored as 46023. The date you see is just formatting applied to that number. Times are stored as fractions of a day: 12:00 noon is 0.5 and 6:00 am is 0.25. A date with a time, such as 1 January 2026 at 6 pm, is 46023.75.

This is why you can subtract one date from another to get the number of days between them, or add 30 to a date to get the date 30 days later. To see the underlying number, select a date and change the format to General with Ctrl + Shift + ~. Change it back with Ctrl + Shift + #.

How Excel reads a typed date depends on your Windows regional settings. In the UAE and UK, 03/04/2026 is usually read as 3 April; in the US it is 4 March. Typing dates in the form 3-Apr-2026 avoids confusion.

TODAY and NOW

Because they change, do not use them to record when something happened. To stamp a fixed date, press Ctrl + ;; for a fixed time, press Ctrl + Shift + ;.

A useful example is days overdue on an invoice: =MAX(0,TODAY()-D2), where D2 is the due date. MAX stops the result going negative for invoices that are not yet due.

Building and splitting dates

DATE is clever with overflow. =DATE(2026,13,1) returns 1 January 2027, and =DATE(2026,3,0) returns the last day of February 2026.

Adding days, months and years

End of month with EOMONTH

=EOMONTH(A2,0) returns the last day of the month for the date in A2. Change the second argument to move forward or back: =EOMONTH(A2,1) is the end of next month, and =EOMONTH(A2,-1)+1 is the first day of the current month.

EOMONTH is ideal for "payment due at end of following month" terms and for monthly reports with SUMIFS, where you need the first and last date of each month as conditions.

Differences between dates with DATEDIF

Subtracting dates gives days. For complete years or months, use DATEDIF. It does not appear in the function list, but it works in every version:

Combine them for a readable result: =DATEDIF(B2,TODAY(),"y")&" years, "&DATEDIF(B2,TODAY(),"ym")&" months". The start date must be earlier than the end date, or DATEDIF returns #NUM!.

Working days with NETWORKDAYS and WORKDAY

=NETWORKDAYS(A2,B2) counts working days between two dates, assuming a Saturday-Sunday weekend. Add a range of public holiday dates as the third argument to exclude them: =NETWORKDAYS(A2,B2,Holidays).

=WORKDAY(A2,10) returns the date 10 working days after A2, useful for delivery promises and service-level deadlines.

For different weekends, use the .INTL versions. The weekend code 7 means Friday-Saturday, and 11 means Sunday only. For example, =NETWORKDAYS.INTL(A2,B2,11,Holidays) counts working days for a business that works Monday to Saturday. You can also give a seven-character pattern such as "0000011", where each 1 marks a non-working day starting from Monday.

Time calculations

Common mistakes to avoid

Summary

Back to: all Excel tutorials.