AI Reading

Quick summary of this article

Preparing the AI summary…

Advanced Excel Chapter 7: Date and Time Functions with Practical Examples

Date and time calculations are essential for attendance sheets, payroll systems, project schedules, employee records, ageing reports, invoice tracking and monthly business reports. Excel stores dates and times as numbers, which allows them to be added, subtracted, compared and analyzed with formulas.

In this chapter, you will learn TODAY, NOW, DATE, DAY, MONTH, YEAR, DATEDIF, YEARFRAC, EDATE, EOMONTH, WORKDAY, WORKDAY.INTL, NETWORKDAYS and NETWORKDAYS.INTL through practical business examples.

Learning Objectives

After completing this chapter, you should be able to:

Understand how Excel stores dates and times.
Display the current date and time.
Create valid dates from separate values.
Extract the day, month and year from a date.
Calculate age and employment duration.
Calculate the difference between two dates.
Add or subtract months from a date.
Find the first or last date of a month.
Calculate deadlines using working days.
Exclude weekends and holidays from calculations.
Create invoice-ageing and attendance reports.
Prepare monthly, quarterly and yearly summaries.

1. How Excel Stores Dates and Times

Excel stores a date as a serial number. In the standard Windows date system, 1 January 1900 is stored as serial number 1. Later dates have larger serial numbers.

Example Date Serial Numbers

Date	Example serial number
01-Jan-2025	45658
01-Jan-2026	46023
18-Jul-2026	46221

The displayed value depends on the cell’s number format. The same underlying value can be displayed as a date or as a serial number.

How Excel Stores Time

Time is stored as a fraction of one complete day.

Time	Stored value
12:00 AM	0
6:00 AM	0.25
12:00 PM	0.5
6:00 PM	0.75

2. Entering Dates Correctly

Dates should be entered in a format that Excel recognizes. The interpretation may depend on the computer’s regional settings.

Recommended Date Entry Examples


18-Jul-2026
18/07/2026
2026-07-18

Recommended Display Format

For clarity, use a format containing a text month:

dd-mmm-yyyy

This displays the date as 18-Jul-2026 and reduces confusion between day-first and month-first formats.

Check Whether a Date Is Valid

Because Excel stores valid dates as numbers, use:

=ISNUMBER(A2)

TRUE generally indicates that Excel recognizes the date as a numerical date value.

3. Create the Employee Dataset

Create a worksheet named Employee_Data and enter the following records:

EmployeeID	EmployeeName	DateOfBirth	JoiningDate	Department	LeaveStart	LeaveEnd
EMP-101	Rahul Sharma	15-Mar-1995	10-Jun-2020	Sales	20-Jul-2026	25-Jul-2026
EMP-102	Priya Singh	22-Aug-1998	05-Jan-2022	Finance	03-Aug-2026	07-Aug-2026
EMP-103	Imran Khan	09-Nov-1992	15-Sep-2018	Marketing	10-Aug-2026	15-Aug-2026
EMP-104	Neha Das	28-Feb-2000	01-Apr-2023	HR	17-Aug-2026	21-Aug-2026
EMP-105	Amit Kumar	12-Dec-1996	20-Jul-2021	Operations	24-Aug-2026	29-Aug-2026

Convert the dataset into an Excel Table and name it EmployeeData.

4. Using the TODAY Function

TODAY returns the current date according to the computer’s system date.

TODAY Syntax

=TODAY()

TODAY does not require any argument.

Display the Current Date

=TODAY()

Display Tomorrow’s Date

=TODAY()+1

Display Yesterday’s Date

=TODAY()-1

Display the Date After 30 Days

=TODAY()+30

Calculate Days Since Joining

=TODAY()-D2

Structured Reference Formula

=TODAY()-[@JoiningDate]

5. Using the NOW Function

NOW returns the current date and time according to the computer’s system clock.

NOW Syntax

=NOW()

Display the Current Date and Time

=NOW()

Display the Current Time Only

=MOD(NOW(),1)

Format the result using a time format such as hh:mm AM/PM.

Display the Current Date Only from NOW

=INT(NOW())

TODAY vs NOW

TODAY	NOW
Returns the current date	Returns the current date and time
Time portion is zero	Includes a fractional time value
Useful for age and due-date calculations	Useful for timestamps and time-based tracking

Important: TODAY and NOW are volatile functions. Their results update when Excel recalculates the workbook; they do not permanently capture the original entry time.

6. Insert a Fixed Date or Time

Use keyboard shortcuts when you need a fixed value that should not change.

Shortcut	Purpose
`Ctrl + ;`	Insert the current date
`Ctrl + Shift + ;`	Insert the current time

7. Using the DATE Function

DATE creates a valid Excel date from separate year, month and day values.

DATE Syntax

=DATE(year,month,day)

Create 18 July 2026

=DATE(2026,7,18)

Create a Date from Separate Cells

Suppose Year is stored in A2, Month in B2 and Day in C2.

=DATE(A2,B2,C2)

Create the First Day of the Current Year

=DATE(YEAR(TODAY()),1,1)

Create the Last Day of the Current Year

=DATE(YEAR(TODAY()),12,31)

Create the First Day of a Selected Month

Suppose B2 contains any date from the selected month.

=DATE(
YEAR(B2),
MONTH(B2),
1
)

8. Handling Overflow with DATE

The DATE function automatically adjusts month and day values that exceed normal limits.

Month 13 Moves into the Next Year

=DATE(2026,13,1)

The result is 1 January 2027.

Day Zero Returns the Previous Month’s Last Day

=DATE(2026,7,0)

The result is 30 June 2026.

Use DATE to Find the Previous Month’s Last Date

=DATE(
YEAR(B2),
MONTH(B2),
0
)

9. Using DAY, MONTH and YEAR

These functions extract individual parts from a valid Excel date.

Extract the Day

=DAY(A2)

Extract the Month Number

=MONTH(A2)

Extract the Year

=YEAR(A2)

Structured Reference Examples


=DAY([@OrderDate])

=MONTH([@OrderDate])

=YEAR([@OrderDate])

Create a Month-Year Label

=TEXT(A2,"mmm-yyyy")

Create a Full Month Name

=TEXT(A2,"mmmm")

Create a Quarter Label

="Q"&ROUNDUP(MONTH(A2)/3,0)

Create a Quarter-Year Label

="Q"&ROUNDUP(MONTH(A2)/3,0)&"-"&YEAR(A2)

10. Using the DATEVALUE Function

DATEVALUE converts a recognizable text date into an Excel serial date.

DATEVALUE Syntax

=DATEVALUE(date_text)

Convert Text to a Date

=DATEVALUE("18-Jul-2026")

Convert a Text Date from a Cell

=DATEVALUE(A2)

Format the result as a date. DATEVALUE depends on recognized date formats and regional settings, so ambiguous dates should be avoided.

11. Calculate the Difference Between Two Dates

Calculate Total Days

=B2-A2

If A2 contains the start date and B2 contains the end date, the result is the number of calendar days between them.

Include Both the Start and End Date

=B2-A2+1

Calculate Days Remaining Until a Deadline

=B2-TODAY()

Create a Deadline Status

=IF(
B2<TODAY(),
"Overdue",
IF(
B2=TODAY(),
"Due Today",
"Pending"
)
)

12. Using the DATEDIF Function

DATEDIF calculates the difference between two dates in years, months or days. It is a legacy compatibility function and may not appear in Excel’s function suggestions, but it remains available in many versions.

DATEDIF Syntax

=DATEDIF(start_date,end_date,unit)

DATEDIF Units

Unit	Meaning
`"Y"`	Complete years
`"M"`	Complete months
`"D"`	Total days
`"YM"`	Remaining months after complete years
`"MD"`	Remaining days after complete months
`"YD"`	Remaining days after complete years

Calculate Employee Age

=DATEDIF(C2,TODAY(),"Y")

Structured Reference Formula

=DATEDIF(
[@DateOfBirth],
TODAY(),
"Y"
)

Calculate Complete Employment Years

=DATEDIF(
[@JoiningDate],
TODAY(),
"Y"
)

Calculate Total Completed Months

=DATEDIF(
[@JoiningDate],
TODAY(),
"M"
)

Calculate Total Days of Service

=DATEDIF(
[@JoiningDate],
TODAY(),
"D"
)

13. Display Age in Years, Months and Days

=DATEDIF(C2,TODAY(),"Y")
&" Years, "&
DATEDIF(C2,TODAY(),"YM")
&" Months, "&
DATEDIF(C2,TODAY(),"MD")
&" Days"

Employment Duration Formula

=DATEDIF(D2,TODAY(),"Y")
&" Years and "&
DATEDIF(D2,TODAY(),"YM")
&" Months"

Important: The start date must not be later than the end date. DATEDIF can produce errors or unintuitive edge-case results, particularly with the "MD" unit, so important calculations should be checked carefully.

14. Using the YEARFRAC Function

YEARFRAC calculates the fraction of a year between two dates. It is useful for financial calculations, employee service periods and prorated benefits.

YEARFRAC Syntax

=YEARFRAC(start_date,end_date,[basis])

Calculate Age as a Decimal

=YEARFRAC(C2,TODAY())

Calculate Complete Age

=INT(YEARFRAC(C2,TODAY()))

Calculate Employment Duration

=YEARFRAC(D2,TODAY(),1)

YEARFRAC Basis Values

Basis	Day-count method
`0`	US 30/360
`1`	Actual/actual
`2`	Actual/360
`3`	Actual/365
`4`	European 30/360

15. Using the EDATE Function

EDATE returns a date a selected number of months before or after another date.

EDATE Syntax

=EDATE(start_date,months)

Add Three Months

=EDATE(A2,3)

Subtract Six Months

=EDATE(A2,-6)

Calculate a One-Year Renewal Date

=EDATE(A2,12)

Calculate a Three-Month Probation End Date

=EDATE(D2,3)

Calculate an EMI Payment Date

Suppose the first payment date is in A2 and the installment number is in B2.

=EDATE(A2,B2-1)

16. Using the EOMONTH Function

EOMONTH returns the final date of a month before or after a selected date.

EOMONTH Syntax

=EOMONTH(start_date,months)

Last Day of the Current Month

=EOMONTH(TODAY(),0)

Last Day of the Previous Month

=EOMONTH(TODAY(),-1)

Last Day of the Next Month

=EOMONTH(TODAY(),1)

First Day of the Current Month

=EOMONTH(TODAY(),-1)+1

First Day of the Next Month

=EOMONTH(TODAY(),0)+1

Calculate an Invoice Due Date at Month-End

=EOMONTH(A2,1)

17. EDATE vs EOMONTH

EDATE	EOMONTH
Returns the same relative day after changing months where possible	Returns the final date of the selected month
Useful for renewals and monthly installments	Useful for month-end reporting and closing dates
`=EDATE(A2,3)`	`=EOMONTH(A2,3)`

18. Using the WEEKDAY Function

WEEKDAY returns a number representing the day of the week.

WEEKDAY Syntax

=WEEKDAY(serial_number,[return_type])

Return Monday as 1 and Sunday as 7

=WEEKDAY(A2,2)

Return the Day Name

=TEXT(A2,"dddd")

Identify Weekend Dates

=IF(
WEEKDAY(A2,2)>5,
"Weekend",
"Working Day"
)

Highlight Weekends with Conditional Formatting

=WEEKDAY(A2,2)>5

19. Using the WORKDAY Function

WORKDAY returns a date before or after a specified number of working days. By default, Saturday and Sunday are treated as weekends.

WORKDAY Syntax

=WORKDAY(start_date,days,[holidays])

Calculate a Deadline After 10 Working Days

=WORKDAY(A2,10)

Calculate Five Working Days Before a Date

=WORKDAY(A2,-5)

Exclude Holidays

Suppose the holiday dates are stored in HolidayData[HolidayDate].

=WORKDAY(
A2,
10,
HolidayData[HolidayDate]
)

Calculate an Invoice Follow-Up Date

=WORKDAY(
[@InvoiceDate],
7,
HolidayData[HolidayDate]
)

20. Using WORKDAY.INTL

WORKDAY.INTL allows you to define a different weekend pattern.

WORKDAY.INTL Syntax

=WORKDAY.INTL(
start_date,
days,
[weekend],
[holidays]
)

Friday and Saturday as Weekends

=WORKDAY.INTL(
A2,
10,
7,
HolidayData[HolidayDate]
)

Sunday Only as the Weekend

=WORKDAY.INTL(
A2,
10,
11,
HolidayData[HolidayDate]
)

Custom Weekend Pattern

A seven-character weekend string can also be used. Each character represents Monday through Sunday. Use 1 for a nonworking day and 0 for a working day.

=WORKDAY.INTL(
A2,
10,
"0000011",
HolidayData[HolidayDate]
)

The pattern 0000011 treats Saturday and Sunday as weekends.

21. Using the NETWORKDAYS Function

NETWORKDAYS counts working days between two dates. Saturday and Sunday are excluded automatically.

NETWORKDAYS Syntax

=NETWORKDAYS(start_date,end_date,[holidays])

Calculate Working Days Between Two Dates

=NETWORKDAYS(A2,B2)

Exclude Holidays

=NETWORKDAYS(
A2,
B2,
HolidayData[HolidayDate]
)

Calculate Employee Leave Days

=NETWORKDAYS(
[@LeaveStart],
[@LeaveEnd],
HolidayData[HolidayDate]
)

Calculate Working Days in the Current Month

=NETWORKDAYS(
EOMONTH(TODAY(),-1)+1,
EOMONTH(TODAY(),0),
HolidayData[HolidayDate]
)

22. Using NETWORKDAYS.INTL

NETWORKDAYS.INTL counts working days using a custom weekend pattern.

NETWORKDAYS.INTL Syntax

=NETWORKDAYS.INTL(
start_date,
end_date,
[weekend],
[holidays]
)

Count Working Days with Friday and Saturday Off

=NETWORKDAYS.INTL(
A2,
B2,
7,
HolidayData[HolidayDate]
)

Count Working Days with Sunday Only Off

=NETWORKDAYS.INTL(
A2,
B2,
11,
HolidayData[HolidayDate]
)

23. Create an Employee Attendance Report

Suppose an attendance worksheet contains the following columns:

Employee ID
Employee Name
Month Start
Month End
Present Days
Approved Leave
Working Days
Absent Days
Attendance Percentage

Calculate Working Days

=NETWORKDAYS(
[@[Month Start]],
[@[Month End]],
HolidayData[HolidayDate]
)

Calculate Absent Days

=MAX(
0,
[@[Working Days]]
-[@[Present Days]]
-[@[Approved Leave]]
)

Calculate Attendance Percentage

=IFERROR(
(
[@[Present Days]]
+[@[Approved Leave]]
)
/[@[Working Days]],
0
)

Calculate Physical Presence Percentage

=IFERROR(
[@[Present Days]]
/[@[Working Days]],
0
)

Assign Attendance Status

=IFS(
[@[Attendance Percentage]]>=95%,
"Excellent",
[@[Attendance Percentage]]>=85%,
"Good",
[@[Attendance Percentage]]>=75%,
"Acceptable",
TRUE,
"Action Required"
)

24. Create an Invoice Ageing Report

An invoice-ageing report identifies overdue invoices and groups them according to the number of outstanding days.

Suggested Invoice Columns

Invoice Number
Customer Name
Invoice Date
Due Date
Invoice Amount
Payment Date
Outstanding Amount
Ageing Days
Ageing Group
Payment Status

Calculate Due Date After 30 Calendar Days

=[@InvoiceDate]+30

Calculate Due Date After 15 Working Days

=WORKDAY(
[@InvoiceDate],
15,
HolidayData[HolidayDate]
)

Calculate Ageing Days

=IF(
[@[Outstanding Amount]]<=0,
0,
MAX(
0,
TODAY()-[@[Due Date]]
)
)

Create an Ageing Group

=IFS(
[@[Ageing Days]]=0,
"Not Overdue",
[@[Ageing Days]]<=30,
"1–30 Days",
[@[Ageing Days]]<=60,
"31–60 Days",
[@[Ageing Days]]<=90,
"61–90 Days",
TRUE,
"Above 90 Days"
)

Create a Payment Status

=IF(
[@[Outstanding Amount]]<=0,
"Paid",
IF(
TODAY()>[@[Due Date]],
"Overdue",
"Pending"
)
)

25. Create a Project Deadline Tracker

Suggested Columns

Task ID
Task Name
Start Date
Duration in Working Days
Deadline
Completion Date
Days Remaining
Status

Calculate the Deadline

=WORKDAY(
[@[Start Date]],
[@[Duration in Working Days]],
HolidayData[HolidayDate]
)

Calculate Days Remaining

=IF(
[@[Completion Date]]<>"",
0,
[@Deadline]-TODAY()
)

Create Task Status

=IF(
[@[Completion Date]]<>"",
"Completed",
IF(
TODAY()>[@Deadline],
"Overdue",
IF(
TODAY()= [@Deadline],
"Due Today",
"In Progress"
)
)
)

26. Monthly, Quarterly and Yearly Reporting

Calculate Sales for a Selected Month

Suppose B2 contains any date from the required month.

=SUMIFS(
SalesData[TotalSales],
SalesData[OrderDate],
">="&EOMONTH(B2,-1)+1,
SalesData[OrderDate],
"<="&EOMONTH(B2,0)
)

Calculate Sales for a Selected Year

Suppose B3 contains the selected year, such as 2026.

=SUMIFS(
SalesData[TotalSales],
SalesData[OrderDate],
">="&DATE(B3,1,1),
SalesData[OrderDate],
"<="&DATE(B3,12,31)
)

Calculate Year-to-Date Sales

=SUMIFS(
SalesData[TotalSales],
SalesData[OrderDate],
">="&DATE(YEAR(TODAY()),1,1),
SalesData[OrderDate],
"<="&TODAY()
)

Calculate Previous-Month Sales

=SUMIFS(
SalesData[TotalSales],
SalesData[OrderDate],
">="&EOMONTH(TODAY(),-2)+1,
SalesData[OrderDate],
"<="&EOMONTH(TODAY(),-1)
)

Calculate Current-Quarter Start Date

=DATE(
YEAR(TODAY()),
3*INT((MONTH(TODAY())-1)/3)+1,
1
)

Calculate Current-Quarter End Date

=EOMONTH(
DATE(
YEAR(TODAY()),
3*INT((MONTH(TODAY())-1)/3)+3,
1
),
0
)

27. Calculate Time Differences

Suppose Start Time is in A2 and End Time is in B2.

Calculate Hours Worked

=(B2-A2)*24

Calculate Minutes Worked

=(B2-A2)*1440

Calculate Seconds

=(B2-A2)*86400

Handle a Shift That Crosses Midnight

=MOD(B2-A2,1)

Format the result as [h]:mm when total hours may exceed 24.

Calculate Pay from Hours Worked

Suppose the hourly rate is stored in C2.

=MOD(B2-A2,1)*24*C2

28. Useful Date and Time Formatting Codes

Format code	Example result
`dd-mm-yyyy`	18-07-2026
`dd-mmm-yyyy`	18-Jul-2026
`dddd`	Saturday
`mmm-yyyy`	Jul-2026
`mmmm`	July
`hh:mm AM/PM`	02:30 PM
`hh:mm:ss`	14:30:45
`[h]:mm`	35:30

29. Common Date and Time Errors

Dates Stored as Text

A date may look correct but still be stored as text. Check it using:

=ISNUMBER(A2)

Day and Month Reversed

The value 07/08/2026 may be interpreted differently under different regional settings. Use unambiguous formats such as 07-Aug-2026.

Negative Date Result

A negative result may occur when the end date is earlier than the start date.

=IF(
B2<A2,
"Check Dates",
B2-A2
)

Hash Symbols Displayed

A cell may display ##### when the column is too narrow or when the formula produces an unsupported negative date or time.

Incorrect Holiday Range

Holiday values must be valid Excel dates. Text descriptions should be stored in a separate column.

TODAY or NOW Not Updating

Check whether workbook calculation is set to Automatic under Formulas → Calculation Options.

30. Date and Time Best Practices

Store dates as real Excel dates, not text.
Use an unambiguous display format such as dd-mmm-yyyy.
Use DATE instead of building dates with text.
Store public and company holidays in a separate table.
Use WORKDAY for deadline dates.
Use NETWORKDAYS for working-day counts.
Use INT when a complete number of years is required from YEARFRAC.
Use EDATE for monthly renewals and installments.
Use EOMONTH for month-end reports.
Use fixed-date shortcuts when the value should not update.
Test formulas around leap years and month-end dates.
Validate start and end dates before calculating differences.

31. Practical Assignment: Employee Leave and Attendance System

Create an Employee Leave and Attendance System containing at least 50 employee records.

Required Worksheets

Employee_Master
Holiday_List
Leave_Records
Monthly_Attendance
Attendance_Summary

Required Calculations

Employee age
Employment duration
Probation completion date
Total leave days
Working days in the month
Present days
Absent days
Attendance percentage
Leave return date
Attendance status

Assignment Tasks

Create an employee master table.
Create a holiday table containing valid dates.
Calculate employee age using DATEDIF.
Calculate service duration in years and months.
Calculate a three-month probation end date using EDATE.
Calculate leave days using NETWORKDAYS.
Exclude holidays from the leave calculation.
Calculate the employee’s return date using WORKDAY.
Calculate working days for a selected month.
Calculate attendance percentage.
Classify attendance as Excellent, Good, Acceptable or Action Required.
Highlight low attendance with conditional formatting.
Create a department-wise attendance summary.

32. Practice Exercises

Exercise 1: Age Calculator

Calculate a person’s age in complete years and then display the detailed age in years, months and days.

Exercise 2: Invoice Due-Date Calculator

Calculate invoice due dates using both 30 calendar days and 20 working days.

Exercise 3: Employee Service Report

Calculate service duration, probation end date and next work anniversary.

Next Work Anniversary

=DATE(
YEAR(TODAY())+
(DATE(YEAR(TODAY()),MONTH(D2),DAY(D2))<TODAY()),
MONTH(D2),
DAY(D2)
)

Exercise 4: Monthly Sales Report

Calculate current-month, previous-month and year-to-date sales using SUMIFS and date boundaries.

Exercise 5: Project Schedule

Calculate task deadlines using working days and a custom holiday list. Highlight overdue tasks automatically.

33. Chapter Quiz

How does Excel store dates?
How does Excel store time?
What is the difference between TODAY and NOW?
Which shortcut inserts a fixed current date?
What is the purpose of the DATE function?
How can you extract a year from a date?
What does the DATEDIF "Y" unit return?
What is the difference between EDATE and EOMONTH?
What does WORKDAY calculate?
What does NETWORKDAYS calculate?
Why should a holiday list contain valid Excel dates?
Which number format should be used for durations above 24 hours?

34. Frequently Asked Questions

Why does Excel treat my date as text?

The entered format may not match the system’s regional settings, or the value may contain spaces or characters. Use DATE or Power Query to create reliable dates.

What is the difference between TODAY and a date entered with Ctrl + ;?

TODAY updates when the workbook recalculates. A date inserted with Ctrl + ; is a fixed value and does not update automatically.

Should I use DATEDIF or YEARFRAC to calculate age?

DATEDIF with the Y unit returns completed years. INT applied to YEARFRAC can also calculate completed years, but the selected basis and date circumstances should be considered.

Does NETWORKDAYS include the start and end dates?

NETWORKDAYS counts both boundary dates when they are working days and are not listed as holidays.

How can I use a weekend other than Saturday and Sunday?

Use WORKDAY.INTL or NETWORKDAYS.INTL and select the appropriate weekend code or custom seven-character pattern.

Why does a time calculation reset after 24 hours?

A normal time format displays time within a 24-hour day. Use the custom format [h]:mm to display total durations longer than 24 hours.

Conclusion

Excel date and time functions make it possible to automate attendance, payroll, invoice ageing, project deadlines, renewals and reporting periods. TODAY and NOW provide the current date and time, while DATE creates reliable dates from separate values.

DATEDIF and YEARFRAC calculate age and duration. EDATE and EOMONTH handle month-based calculations, while WORKDAY and NETWORKDAYS calculate deadlines and durations using business working days.

In the next chapter, we will learn Data Validation and Conditional Formatting, including dropdown lists, dependent dropdowns, custom validation rules, duplicate detection, formula-based formatting and KPI indicators.