Excel Date and Time Functions: A Practical Guide

Excel can calculate with dates and times because it stores them as numbers. A date is represented by a whole-number serial value, while a time is stored as a fraction of a day. The format applied to the cell controls whether you see a date, a clock time, or the underlying number.

That model explains most date-and-time formulas. Add 7 to a valid date and you move forward seven calendar days. Subtract two dates and you get the number of days between them. Functions such as DATE, EDATE, WORKDAY, and TIME handle cases where simple addition is not enough.

Choose the function by the result you need

Microsoft’s date and time function reference lists the full function family. The sections below focus on the functions that solve the most common scheduling and reporting tasks.

Understand the value behind the format

A date-and-time value has two parts. The whole-number portion represents the date; the decimal portion represents the time of day. For example, 0.5 represents noon because it is half of a 24-hour day.

The same Excel date and time value displayed as a formatted date and as its underlying serial number.

This produces three useful consequences:

• Adding 1 to a date moves it forward one day.
• Adding 0.5 to a timestamp moves it forward 12 hours.
• Subtracting an earlier timestamp from a later one returns the elapsed portion of a day.

A cell that displays 46280 may already contain a valid date; it may simply have a General or Number format. Changing the number format changes the display, not the stored value.

Do not judge a date solely by how it looks. Select the cell and inspect the formula bar, cell format, and underlying value before rewriting a formula.

Build reliable values with DATE and TIME

The DATE function combines year, month, and day numbers. Its syntax is DATE(year,month,day). The following formula creates September 15, 2026 without relying on a regional typing order:

=DATE(2026,9,15)

Use four digits for the year. Two-digit years can be interpreted according to system settings, while a four-digit year states the intended century.

DATE also normalizes month and day values outside their usual range. For example, this formula returns January 5, 2027 because month 13 rolls into the next year:

=DATE(2026,13,5)

That behavior is useful in deliberate date arithmetic, but it can also hide a bad input. Validate source values when a user is supposed to enter a month from 1 through 12. Microsoft documents the argument rules in its DATE function reference.

The TIME function combines hour, minute, and second numbers. Its syntax is TIME(hour,minute,second):

=TIME(14,30,0)

Formatted as a time, the result displays as 2:30 PM or 14:30, depending on the chosen number format. To create one timestamp, add the date and time values:

Use DATE and TIME separately or combine them to return a complete Excel timestamp.

=DATE(2026,9,15)+TIME(14,30,0)

The result is September 15, 2026 at 2:30 PM. TIME can carry excess positive minutes or seconds into the next unit, but it represents a time of day rather than a duration longer than 24 hours.

Extract parts from a date or timestamp

Assume A2 contains September 15, 2026 at 2:30:45 PM. These functions return individual components:

Use YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND to return individual components from a timestamp.

These functions return numbers, not formatted labels. MONTH(A2) returns 9, not September. If you only need to display a month name, a number format may be better than creating a separate text value.

Time-part functions also return components, not totals. If a duration is 26 hours and 30 minutes, HOUR does not return 26. Use duration arithmetic or an elapsed-time number format instead.

Use TODAY and NOW for values that update

TODAY() returns the current date. NOW() returns the current date and time. Neither function accepts an argument:

TODAY returns the current date, while NOW returns the current date and time.

=TODAY()
=NOW()

Both results depend on the computer’s current date and time and update when the worksheet recalculates. NOW() is not a continuously running clock.

This distinction matters in logs and audit sheets. Use TODAY() or NOW() when the value should remain current. Enter or paste a fixed date or timestamp when the record must preserve the moment it was created.

Microsoft’s references for TODAY and NOW explain their recalculation behavior.

Calculate calendar deadlines and intervals

When A2 contains a valid date, add or subtract whole numbers to move by calendar days:

Use simple date arithmetic in Excel to add or subtract calendar days and calculate the number of days between two dates.

=A2+14
=A2-7

The first formula returns a date 14 calendar days later; the second returns a date seven calendar days earlier. To find the elapsed number of days between dates in A2 and B2, subtract the earlier date from the later one:

=B2-A2

Format the result as General or Number when you want a day count rather than another date display.

Move by months with EDATE

A calendar month is not a fixed number of days. Use EDATE when a due date should move by whole months:

Use EDATE to move by whole calendar months and EOMONTH to return the last day of a month.

=EDATE(A2,3)
=EDATE(A2,-1)

The first formula returns a date three months after A2; the second returns a date one month before it. A positive month argument moves forward, and a negative argument moves backward.

Find month end with EOMONTH

EOMONTH returns the last day of a month relative to a starting date:

=EOMONTH(A2,0)
=EOMONTH(A2,1)

Use 0 for the end of the starting month, 1 for the end of the next month, and -1 for the end of the previous month. This is clearer and safer than assuming every month has the same length.

Microsoft’s guide to adding and subtracting dates covers calendar days, months, years, and combinations of date parts.

Calculate working-day schedules

Simple addition counts every calendar day. Use WORKDAY when weekends and holidays should not count toward a deadline:

Use WORKDAY to calculate a deadline based on working days and NETWORKDAYS to count working days between two dates.

=WORKDAY(A2,10,$H$2:$H$10)

This returns the date 10 working days after the start date in A2. The optional range $H$2:$H$10 contains holiday dates to exclude. Store actual Excel dates in that range, not labels that merely look like dates.

Use NETWORKDAYS for the opposite question: how many whole working days fall between a start date and an end date?

=NETWORKDAYS(A2,B2,$H$2:$H$10)

The standard functions treat Saturday and Sunday as weekend days. If your weekend falls on different days, use WORKDAY.INTL or NETWORKDAYS.INTL and specify the appropriate weekend rule. See Microsoft’s WORKDAY reference for argument details.

Add time and calculate elapsed hours

To add 2 hours and 45 minutes to a timestamp in A2, add a TIME result:

=A2+TIME(2,45,0)

To calculate elapsed time, subtract the start timestamp from the end timestamp:

=B2-A2

If a task starts at 8:00 AM on one day and ends at 10:30 AM the next day, the difference is 26 hours and 30 minutes. Apply the custom number format [h]:mm to display 26:30. A standard h:mm clock format wraps after 24 hours and would hide the complete duration.

Use an elapsed-time format such as [h]:mm to display durations longer than 24 hours without wrapping.

To return decimal hours for further calculation, multiply the day fraction by 24:

=(B2-A2)*24

The same principle uses 1440 for total minutes and 86400 for total seconds. Microsoft’s guide to date and time number formats documents the bracketed elapsed-time formats.

Format dates without turning them into text

A number format changes presentation while preserving the date value for sorting, filtering, and arithmetic. Useful custom formats include:

Regional settings affect how Excel interprets typed date text and which default format it displays. When a formula needs a fixed date, DATE(2026,9,15) is safer than an ambiguous text literal such as "9/15/26" or "15/9/26".

Troubleshoot date and time formulas

The 1900 and 1904 systems differ by 1,462 days. Microsoft explains the mismatch and correction options in its guide to Excel date systems.

Changing a workbook’s date system affects existing date values. Diagnose the mismatch and review dependent formulas before changing that workbook setting.

A dependable workflow for date calculations

1. Confirm that each input is a real Excel date or time value, not text that only looks correct.
2. Choose the unit: calendar days, months, working days, clock time, or elapsed time.
3. Use direct arithmetic for calendar days and elapsed intervals.
4. Use EDATE, EOMONTH, or WORKDAY when calendar rules matter.
5. Apply the number format that matches the intended display.
6. Test month-end dates, leap years, weekends, holidays, and intervals longer than 24 hours when they are relevant.

Frequently asked questions

Separate the calculation from the display

Reliable date and time workbooks keep two questions separate: what value should Excel calculate, and how should that value appear? Build valid values with DATE and TIME, choose functions that match the calendar rule, then apply formatting at the end.

When a result looks wrong, inspect the underlying number before changing the formula. Many apparent date errors are really text inputs, ambiguous regional formats, or correct serial values displayed with the wrong number format.