MS Excel - Excel Date and Time Functions

Excel date and time functions are used to store, calculate, compare, and manipulate dates and times. They are especially useful when working with employee records, attendance, project schedules, invoices, deadlines, financial reports, and other time-based data.

Excel internally stores dates as serial numbers. This allows Excel to perform calculations such as finding the number of days between two dates or adding a specific number of days to a date. Time values are stored as fractions of a day.

For example, a date such as 10-Sep-2026 can be used in mathematical calculations, even though Excel displays it in a readable date format.

1. Understanding Excel Dates

Excel treats a date as a numerical value internally.

For example:

10-Sep-2026

can be used in calculations such as:

=A2+30

If A2 contains 10-Sep-2026, the formula returns the date that occurs 30 days later.

This makes Excel particularly useful for calculating deadlines, due dates, expiry dates, and project durations.

2. TODAY Function

The TODAY function returns the current date according to the computer's system date.

Syntax

=TODAY()

There are no arguments inside the parentheses.

For example:

=TODAY()

might display:

10-Sep-2026

The displayed date changes automatically when the worksheet is recalculated on a different day.

Uses of TODAY

The TODAY function can be useful for:

  • Creating reports containing the current date

  • Calculating employee age

  • Monitoring deadlines

  • Calculating days remaining

  • Tracking subscription or contract periods

  • Creating daily business reports

For example, if A2 contains a project deadline:

=A2-TODAY()

This calculates the number of days between today and the deadline.

3. NOW Function

The NOW function returns the current date and time.

Syntax

=NOW()

For example, it might return:

10-Sep-2026 08:50

Unlike TODAY, which returns only the date, NOW returns both the date and the current time.

It is useful when a worksheet needs to display when a calculation or report was generated.

Difference Between TODAY and NOW

Function Returns
TODAY() Current date
NOW() Current date and current time

Both functions update when Excel recalculates the worksheet.

4. DATE Function

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

Syntax

=DATE(year,month,day)

For example:

=DATE(2026,9,10)

returns:

10-Sep-2026

This function is useful when year, month, and day are stored separately in different cells.

Suppose:

A2 = 2026
B2 = 9
C2 = 10

The following formula combines them:

=DATE(A2,B2,C2)

The result is:

10-Sep-2026

5. DAY Function

The DAY function extracts the day number from a date.

Syntax

=DAY(serial_number)

If A2 contains:

10-Sep-2026

then:

=DAY(A2)

returns:

10

This is useful when individual date components are required for calculations or reporting.

6. MONTH Function

The MONTH function extracts the month number from a date.

For example:

=MONTH(A2)

If A2 contains:

10-Sep-2026

the result is:

9

because September is the ninth month.

7. YEAR Function

The YEAR function extracts the year from a date.

For example:

=YEAR(A2)

If A2 contains:

10-Sep-2026

the result is:

2026

DAY, MONTH, and YEAR can therefore be used together to break a date into its individual components.

8. DATEVALUE Function

DATEVALUE converts a date stored as text into an Excel date value.

For example, suppose A2 contains a text representation such as:

10/09/2026

A formula such as:

=DATEVALUE(A2)

can convert the recognized date text into an Excel date value.

This can be useful when importing data from external systems where dates may have been stored as text rather than as actual Excel dates.

The interpretation of date text can depend on regional date settings, so imported dates should be checked carefully.

9. TIME Function

The TIME function creates a time value from separate hour, minute, and second values.

Syntax

=TIME(hour,minute,second)

For example:

=TIME(14,30,0)

returns:

2:30 PM

This is useful when hours, minutes, and seconds are stored separately.

Suppose:

A2 = 14
B2 = 30
C2 = 0

You can combine them with:

=TIME(A2,B2,C2)

10. HOUR Function

The HOUR function extracts the hour from a time value.

For example:

=HOUR(A2)

If A2 contains:

14:30:00

the result is:

14

11. MINUTE Function

The MINUTE function extracts the minute portion of a time.

For example:

=MINUTE(A2)

If A2 contains:

14:30:45

the result is:

30

12. SECOND Function

The SECOND function extracts the seconds from a time value.

For example:

=SECOND(A2)

If A2 contains:

14:30:45

the result is:

45

13. Calculating the Difference Between Dates

One of the most useful applications of Excel date functions is calculating the duration between two dates.

Suppose:

A2 = 01-Sep-2026
B2 = 10-Sep-2026

You can calculate the difference using:

=B2-A2

The result is:

9

This means nine days have passed between the two dates.

14. Calculating Age with YEARFRAC

YEARFRAC calculates the fraction of a year represented by the number of whole days between two dates.

Syntax

=YEARFRAC(start_date,end_date)

For example:

=YEARFRAC(A2,TODAY())

can be used to calculate the number of years, including a fractional portion, between a starting date and today.

For age calculations, the result may need to be rounded down depending on the required presentation.

15. Calculating Working Days

Excel provides functions for calculating working days while excluding weekends and, when required, holidays.

NETWORKDAYS calculates the number of working days between two dates.

Syntax

=NETWORKDAYS(start_date,end_date,[holidays])

For example:

=NETWORKDAYS(A2,B2)

calculates the working days between the dates in A2 and B2, normally excluding Saturdays and Sundays.

If holidays are stored in cells E2:E5:

=NETWORKDAYS(A2,B2,E2:E5)

the specified holidays are also excluded.

This is useful for:

  • Employee work schedules

  • Project planning

  • Leave calculations

  • Delivery schedules

  • Business-day calculations

16. WORKDAY Function

WORKDAY calculates a date that occurs a specified number of working days before or after another date.

Syntax

=WORKDAY(start_date,days,[holidays])

For example:

=WORKDAY(A2,10)

returns the date 10 working days after the date in A2.

If holidays are listed in E2:E5:

=WORKDAY(A2,10,E2:E5)

Excel excludes the specified holidays from the calculation.

17. EDATE Function

EDATE returns a date that is a specified number of months before or after a starting date.

Syntax

=EDATE(start_date,months)

For example:

=EDATE(A2,3)

returns a date three months after the date in A2.

This is useful for:

  • Subscription renewal dates

  • Contract expiry dates

  • Installment schedules

  • Monthly reporting

  • Membership periods

18. EOMONTH Function

EOMONTH returns the last day of a month that occurs a specified number of months before or after a given date.

Syntax

=EOMONTH(start_date,months)

For example:

=EOMONTH(A2,0)

returns the last day of the month containing the date in A2.

If A2 contains a date in September 2026, the result would be:

30-Sep-2026

This is particularly useful when preparing monthly financial or business reports.

19. WEEKDAY Function

The WEEKDAY function returns a number representing the day of the week for a date.

Syntax

=WEEKDAY(serial_number,[return_type])

For example:

=WEEKDAY(A2)

returns a number corresponding to the weekday according to the selected return type.

Different return types allow you to determine how Excel numbers the days of the week.

This can be useful for identifying weekdays and weekends.

20. WEEKNUM Function

The WEEKNUM function returns the week number for a particular date.

For example:

=WEEKNUM(A2)

can determine which week of the year contains the date in A2.

This is useful for weekly sales reports, production schedules, project tracking, and business analysis.

21. Formatting Dates and Times

Date and time functions produce values that can be displayed using different formats.

For example, the same date can be displayed as:

10-Sep-2026

or:

10/09/2026

or:

September 10, 2026

The underlying date value remains the same; only its display format changes.

Similarly, a time can be displayed as:

14:30

or:

2:30 PM

Users can modify date and time formatting through Excel's cell-formatting options.

22. Adding and Subtracting Time

Excel can perform calculations with time values.

Suppose A2 contains:

09:00 AM

and B2 contains:

05:30 PM

The difference can be calculated using:

=B2-A2

The result represents the elapsed time.

If the result is intended to show durations that may exceed 24 hours, an appropriate duration format such as:

[h]:mm

can be used.

This prevents Excel from displaying only the remainder after complete 24-hour periods.

23. Practical Example: Employee Attendance

Consider the following worksheet:

Employee Login Time Logout Time Working Hours
Anil 9:00 AM 5:30 PM 8:30
Priya 9:15 AM 6:00 PM 8:45
Ravi 8:45 AM 5:15 PM 8:30

If the login time is stored in B2 and logout time in C2, working hours can be calculated using:

=C2-B2

The formula can then be copied down for other employees.

This eliminates the need to manually calculate working hours.

24. Practical Example: Project Deadline

Suppose:

A2 = Project Start Date
B2 = Project Deadline

The number of calendar days available can be calculated with:

=B2-A2

The number of working days can be calculated with:

=NETWORKDAYS(A2,B2)

If the current date needs to be compared with the deadline:

=B2-TODAY()

can calculate the number of days between today and the deadline.

25. Common Errors and Precautions

Date and time calculations can sometimes produce unexpected results.

One common problem occurs when a date is stored as text instead of an actual Excel date. In such cases, arithmetic operations may not work correctly.

Another issue is regional date formatting. For example, 05/06/2026 may be interpreted differently depending on whether the system expects the day-month-year or month-day-year format.

It is also important to distinguish between a date and a date formatted as text. Changing the appearance of a cell does not necessarily convert text into a genuine Excel date.

Summary

Excel's date and time functions make it possible to perform sophisticated calculations involving calendars, deadlines, schedules, working days, durations, and time periods.

Important functions include:

Function Purpose
TODAY() Returns the current date
NOW() Returns the current date and time
DATE() Creates a date from year, month, and day
DAY() Extracts the day
MONTH() Extracts the month
YEAR() Extracts the year
DATEVALUE() Converts recognized date text into a date value
TIME() Creates a time from hour, minute, and second
HOUR() Extracts the hour
MINUTE() Extracts the minute
SECOND() Extracts the seconds
YEARFRAC() Calculates the fractional number of years between dates
NETWORKDAYS() Calculates working days between dates
WORKDAY() Calculates a date after or before working days
EDATE() Moves a date by a specified number of months
EOMONTH() Returns the end of a month
WEEKDAY() Returns the weekday number
WEEKNUM() Returns the week number

These functions are valuable for automating date-related tasks and reducing manual calculations in Excel.