📎 This article includes 1 downloadable practice file ↓

📘 Excel Beginner Course · Lesson 13 of 18

Due dates, ageing, employee service, “sales this month”: half of office Excel is date work. It’s easy once you know one secret: to Excel, a date is just a number.

Dates are numbers

Excel counts days from 1 January 1900. 4 October 2026 is stored as 46299; formatting makes it look like a date. Type a date, then change the format to General and you’ll see the number. That’s why you can subtract dates and add days:

=D2+30            ' due date: 30 days after the invoice date
=B6-D2            ' days between two dates
=D2-7             ' a week earlier

Times are fractions of a day: 12:00 noon is 0.5.

Today’s date

=TODAY()          ' today's date, updates every day
=NOW()            ' date and time

Use TODAY() for “days overdue as of now”. For month-end reports put a fixed as-on date in a cell instead, so the numbers don’t change when you open the file next month. (Ctrl + ; types today’s date as a fixed value.)

Pulling dates apart and building them

=YEAR(A2)   =MONTH(A2)   =DAY(A2)
=WEEKDAY(A2)               ' 1 = Sunday ... 7 = Saturday
=TEXT(A2, "mmmm")          ' month name: October
=TEXT(A2, "dddd")          ' day name: Sunday
=DATE(2026, 10, 4)         ' build a date from year, month, day

DATE is safer than typing dates inside formulas, which depend on regional settings.

Months and month-ends

=EDATE(A2, 1)              ' same day next month (handles 31st → 30th)
=EDATE(A2, -3)             ' three months earlier
=EOMONTH(A2, 0)            ' last day of this month
=EOMONTH(A2, 0) + 1        ' first day of next month
=EOMONTH(A2, -1) + 1       ' first day of this month

Adding 30 days is not the same as adding a month. For EMI dates, renewals and month-based due dates, use EDATE.

Age and service: DATEDIF

=DATEDIF(B2, TODAY(), "y")      ' completed years (age, service)
=DATEDIF(B2, TODAY(), "ym")     ' extra months after the full years
=DATEDIF(B2, TODAY(), "md")     ' extra days after the months
=DATEDIF(C2, TODAY(), "y") & " yrs " & DATEDIF(C2, TODAY(), "ym") & " months"

DATEDIF doesn’t appear in Excel’s suggestions (it’s an old function kept for compatibility), but it works in every version. The start date must be before the end date, or you get #NUM!.

Working days

=NETWORKDAYS(A2, B2)                 ' Mon-Fri days between two dates, both ends counted
=NETWORKDAYS(A2, B2, Holidays)       ' also skip a list of holiday dates
=WORKDAY(A2, 10, Holidays)           ' date 10 working days later

For a 6-day week or other weekends, use NETWORKDAYS.INTL and WORKDAY.INTL; see working days, due dates and holidays.

Indian financial year (April–March)

=YEAR(A2) - (MONTH(A2) < 4)                            ' FY start year: Feb 2027 → 2026
="FY " & (YEAR(A2)-(MONTH(A2)<4)) & "-" & RIGHT(YEAR(A2)-(MONTH(A2)<4)+1, 2)   ' FY 2026-27

(MONTH(A2)<4) is TRUE (1) for January–March, so those months belong to the previous year’s FY.

Where beginners go wrong

Practice

Download this lesson’s workbook below. The People sheet has birth, joining and invoice dates with a fixed as-on date of 04-Oct-2026. Type your answers in the yellow column; the Check column turns green when you’re right, and the Answers sheet shows a working formula for every task.