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
=TODAY()returns the current date and updates every time the workbook recalculates.=NOW()returns the current date and time.
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(2026,12,31)builds a date from year, month and day. It is the safest way to write a date inside a formula.=YEAR(A2),=MONTH(A2)and=DAY(A2)extract parts of a date.=WEEKDAY(A2,2)returns 1 for Monday through 7 for Sunday.=TEXT(A2,"mmmm yyyy")shows a date as text such as "November 2026", which is handy for labels. Use"ddd"for the short day name.
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
- Days: just add.
=A2+30gives a date 30 days later, perfect for 30-day payment terms. - Months: use EDATE.
=EDATE(A2,3)returns the same day three months later, and=EDATE(A2,-1)goes back one month. If the day does not exist, such as 31 February, EDATE returns the last day of the month. - Years: use EDATE with multiples of 12.
=EDATE(A2,12)is useful for licence and visa renewal reminders.
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:
=DATEDIF(B2,TODAY(),"y")gives completed years, such as an employee's length of service or a person's age.=DATEDIF(B2,TODAY(),"m")gives completed months.=DATEDIF(B2,TODAY(),"ym")gives months left over after the whole years.
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
- Hours worked:
=C2-B2where B2 is the start time and C2 the finish. Format the result ash:mm. - Shifts past midnight:
=MOD(C2-B2,1)gives the right answer when the finish time is after midnight. - Total hours over 24: format the total with the custom format
[h]:mm; without the square brackets, Excel rolls over every 24 hours. - Hours as a decimal for payroll: multiply by 24.
=(C2-B2)*24turns 8:30 into 8.5, ready to multiply by an hourly rate.
Common mistakes to avoid
- Dates stored as text. If dates sit on the left of the cell by default, they are probably text. Convert them before calculating.
- Results shown as dates. Subtracting two dates sometimes displays a date such as 30/01/1900. Change the format to General or Number.
- Using TODAY for records. It changes daily; use the keyboard shortcut for fixed dates.
- Missing holidays. NETWORKDAYS ignores public holidays unless you list them.
Summary
- Dates are numbers counted from 1900; times are fractions of a day.
- Use DATE to write dates in formulas and EDATE to add months.
- EOMONTH finds month ends; DATEDIF gives completed years and months.
- NETWORKDAYS and WORKDAY skip weekends and holidays; use .INTL for other weekends.
- Use [h]:mm for totals over 24 hours and multiply by 24 for decimal hours.
Back to: all Excel tutorials.