Excel Date and Time Functions

Dates and times are a common type of data in Excel — found in invoices, reports, schedules, and timesheets. Excel stores dates as serial numbers internally (for example, January 1, 1900 = 1), which makes it possible to perform calculations with dates, such as finding how many days have passed or adding a number of days to a date.

How Excel Stores Dates

Excel treats dates as numbers. For example:

• January 1, 2024 is stored as the number 45292.
• The date "looks" like a date only because of formatting.

This means that subtracting two dates gives the number of days between them:

  =B2-A2   (if A2 = 01/01/2024 and B2 = 10/01/2024)
  Result: 9   (9 days between the two dates)

TODAY and NOW Functions

TODAY()

Returns the current date. The value updates automatically every time the spreadsheet is opened or recalculated.

  =TODAY()
  Result: 15/03/2025  (depends on the current date when opened)

NOW()

Returns the current date and time.

  =NOW()
  Result: 15/03/2025 14:32

Neither function requires arguments — the parentheses are left empty.

DATE Function

What It Does

Creates a date from separate year, month, and day values stored in different cells or typed directly.

Syntax

  =DATE(year, month, day)

Example

  =DATE(2024, 6, 15)
  Result: 15/06/2024

  If year is in A2, month in B2, day in C2:
  =DATE(A2, B2, C2)

YEAR, MONTH, DAY Functions

These three functions extract individual parts from a date.

YEAR

  =YEAR(date)
  =YEAR("15/06/2024")
  Result: 2024

MONTH

  =MONTH(date)
  =MONTH("15/06/2024")
  Result: 6

DAY

  =DAY(date)
  =DAY("15/06/2024")
  Result: 15

Practical Use

  If A2 contains the date 15/06/2024:
  =YEAR(A2)  → 2024
  =MONTH(A2) → 6
  =DAY(A2)   → 15

WEEKDAY Function

What It Does

Returns a number representing the day of the week for a given date.

Syntax

  =WEEKDAY(date, return_type)

The most common return_type is 2, where Monday=1 and Sunday=7.

Example

  =WEEKDAY("15/06/2024", 2)
  Result: 6   (Saturday)

Display Day Name with TEXT

  =TEXT("15/06/2024", "dddd")
  Result: "Saturday"

TEXT Function for Dates

What It Does

Converts a date or number to text in a specified format. Useful for displaying dates in a readable form inside text strings.

Syntax

  =TEXT(value, format_text)

Common Date Format Codes

• dd: Day as 2 digits (e.g., 05)
• ddd: Abbreviated day name (e.g., Mon)
• dddd: Full day name (e.g., Monday)
• mm: Month as 2 digits (e.g., 06)
• mmm: Abbreviated month name (e.g., Jun)
• mmmm: Full month name (e.g., June)
• yyyy: 4-digit year (e.g., 2024)

Example

  =TEXT(TODAY(), "dd-mmm-yyyy")
  Result: "15-Jun-2024"

  =TEXT(TODAY(), "dddd, mmmm dd, yyyy")
  Result: "Saturday, June 15, 2024"

DATEDIF Function

What It Does

Calculates the difference between two dates in years, months, or days.

Syntax

  =DATEDIF(start_date, end_date, unit)

Unit Options

• "Y": Complete years between dates.
• "M": Complete months between dates.
• "D": Complete days between dates.
• "YM": Months, ignoring years.
• "MD": Days, ignoring months and years.

Example: Calculate Age

  Date of birth in A2: 15/06/1990
  Today's date: =TODAY()

  =DATEDIF(A2, TODAY(), "Y")
  Result: 34   (age in complete years)

Full Age Display

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

  Result: "34 Years, 2 Months, 5 Days"

EDATE and EOMONTH Functions

EDATE

Returns a date that is a specified number of months before or after a start date.

  =EDATE(start_date, months)

  =EDATE("01/01/2024", 3)
  Result: 01/04/2024  (3 months later)

  =EDATE("01/01/2024", -1)
  Result: 01/12/2023  (1 month earlier)

EOMONTH

Returns the last day of a month that is a specified number of months away.

  =EOMONTH("01/01/2024", 0)
  Result: 31/01/2024  (last day of January)

  =EOMONTH("01/01/2024", 1)
  Result: 29/02/2024  (last day of February 2024)

Working with Time

Excel stores time as a decimal fraction of a day. For example, 0.5 represents 12:00 PM (noon), because noon is halfway through a 24-hour day.

TIME Function

  =TIME(hour, minute, second)
  =TIME(9, 30, 0)
  Result: 9:30 AM

HOUR, MINUTE, SECOND Functions

  Cell A2: 09:45:00

  =HOUR(A2)   → 9
  =MINUTE(A2) → 45
  =SECOND(A2) → 0

Calculating Working Hours

  Start time in A2: 09:00 AM
  End time in B2:   05:30 PM

  =B2-A2  → Displays: 8:30  (8 hours and 30 minutes)

  To see as decimal hours:
  =(B2-A2)*24  → 8.5  (8.5 hours)

Summary

• Excel stores dates as serial numbers, allowing date arithmetic (adding days, subtracting dates).
• TODAY() returns the current date; NOW() returns current date and time.
• DATE(y,m,d): Creates a date; YEAR(), MONTH(), DAY(): Extract parts of a date.
• DATEDIF: Calculates the difference between two dates in years, months, or days.
• TEXT: Formats a date as readable text (e.g., "15-Jun-2024").
• EDATE: Adds or subtracts months from a date; EOMONTH: Finds the last day of a month.
• Time is stored as fractions of a day; time arithmetic and functions follow the same principles as date functions.