Date & Time Functions

Dates are everywhere in office work: joining dates, invoice due dates, EMI schedules, attendance and leave. Excel handles them well once you know one secret: every date is really a number. With that in mind, adding 30 days to a date or counting working days becomes simple arithmetic.

In this lesson you will learn how dates and times are stored, build and take apart dates, calculate ages and service periods, add months, count working days with Indian holidays and six-day weeks, and work out the April to March financial year.

Dates are numbers

Excel stores a date as the number of days since 1 January 1900, which is day 1. So 30 October 2026 is stored as 46325. The date you see is just a number format applied to that value. Type a date, change the cell's format to General, and the serial number appears.

Times are stored as fractions of a day: 0.5 is 12:00 noon, 0.25 is 6:00 AM, and 46325.75 is 30 October 2026 at 6:00 PM.

Because dates are numbers, you can subtract them. With 15-Jun-2021 in C2 and 30-Oct-2026 in F1:

=F1-C2

1963

That is the number of days between the two dates. If the result shows as a strange date instead, change the cell format to General or Number.

Today's date and building dates

TODAY() is perfect for "days remaining" columns, but it is wrong for records: a bill dated with TODAY() shows a new date tomorrow. For a fixed date, press Ctrl+; to type today's date as a value.

DATE is also smart about overflow: =DATE(2026,13,1) gives 1 January 2027, and =DATE(2026,11,0) gives the last day of October. This makes it useful inside other formulas.

The sample sheet

The examples below use an HR sheet from a small firm in Pune. To keep the answers fixed, today's date is typed into F1 as 30-Oct-2026. In a real sheet, F1 would hold =TODAY().

Age and service with DATEDIF

Subtracting and dividing by 365 gives a slightly wrong age because of leap years. DATEDIF counts complete years, months or days correctly:

=DATEDIF(B2, $F$1, "Y")

Meera: 28
Farhan: 25
Isha: 30

The third argument sets the unit: "Y" for complete years, "M" for complete months, "D" for days. For years of service, use the joining date: =DATEDIF(C2,$F$1,"Y") gives 5 for Meera, and "M" gives 64 months.

Adding months: EDATE and EOMONTH

Adding 30 days is not the same as adding one month. EDATE adds whole months and handles short months correctly:

=EDATE(C3, 6)

01-May-2025

Farhan joined on 1 November 2024, so his six-month probation ends on 1 May 2025. EDATE is also how you build an EMI schedule: the due date of instalment n is =EDATE(first_due_date, n-1). If a start date is 31 January, one month later is 28 February, not 3 March.

EOMONTH returns the last day of a month, a given number of months away. =EOMONTH(F1, 0) gives 31-Oct-2026 (this month's salary date), and =EOMONTH(F1, 1) gives 30-Nov-2026.

Both functions return serial numbers, so format the cells as dates.

Working days, holidays and six-day weeks

NETWORKDAYS counts working days between two dates, assuming Saturday and Sunday are off. A third argument takes a range of holiday dates. With 1-Oct-2026 in H1, 31-Oct-2026 in H2, and Gandhi Jayanti (2-Oct-2026) in H4:

=NETWORKDAYS(H1, H2, H4)

21

October 2026 has 22 weekdays, and the holiday falls on a Friday, leaving 21.

Many Indian shops and offices work six days a week. NETWORKDAYS.INTL lets you choose the weekend. Weekend code 11 means "Sunday only":

=NETWORKDAYS.INTL(H1, H2, 11, H4)

26

You can also give the weekend as a seven-character text of 0s and 1s, starting from Monday, where 1 means a day off: "0000001" is Sunday only, and "0000011" is Saturday and Sunday.

To find a date a number of working days ahead, use WORKDAY: =WORKDAY(F1, 10) returns 13-Nov-2026, ten working days after 30 October (ignoring holidays). WORKDAY.INTL accepts the same weekend codes.

The Indian financial year

The Indian financial year runs from 1 April to 31 March. A date from April onwards belongs to the financial year starting that calendar year; a date from January to March belongs to the one that started the year before. With a date in A2:

=IF(MONTH(A2)>=4, YEAR(A2), YEAR(A2)-1)

For 30-Oct-2026 this gives 2026. To show the usual label, put that formula in B2 and join the parts:

="FY " & B2 & "-" & RIGHT(B2+1, 2)

FY 2026-27

The financial quarter (April to June is Q1) is =ROUNDUP((MOD(MONTH(A2)-4,12)+1)/3, 0), which returns 3 for October.

Working with times

Because times are fractions of a day, subtracting two times gives a fraction. Multiply by 24 to get hours. With 09:30 in B2 and 18:15 in C2:

=(C2-B2)*24

8.75

For a night shift from 22:00 to 06:00, the plain subtraction is negative. MOD wraps it past midnight: =MOD(C2-B2, 1)*24 returns 8.

Common mistakes

• Dates typed as text. "30.10.2026" with dots is usually stored as text, and date functions return #VALUE!. Use hyphens or slashes that match your settings, or DATE.
• Age from dividing by 365. Leap years make it wrong near birthdays. Use DATEDIF.
• Adding 30 for a month. Months have 28 to 31 days. Use EDATE.
• TODAY() on bills and records. It changes every day. Use Ctrl+; for a fixed date.
• A result that looks like a date when you wanted a number. Change the format to General.

Key points

• A date is a serial number (1 = 1 January 1900) and a time is a fraction of a day.
• Subtract dates to get days; use DATEDIF for complete years and months.
• EDATE adds months, EOMONTH finds month ends, and DATE builds dates safely.
• NETWORKDAYS and WORKDAY count working days; the .INTL versions handle six-day weeks.
• The Indian financial year is IF(MONTH(d)>=4, YEAR(d), YEAR(d)-1).

=EDATE($B$1, A3-1)
=TEXT(B3, "dddd")

B3:  05-Nov-2026 (Thursday)
B4:  05-Dec-2026 (Saturday)
B14: 05-Oct-2027 (Tuesday)