Step-by-Step Examples of Using Date Formulas in Excel

Excel includes a “Date and Time” category in the function library for working with dates. Let’s explore some of the most commonly used functions in this category.



How Excel Handles Time

Excel interprets dates and times as numeric values. The spreadsheet converts these data by equating a full day to one whole number. Consequently, the time represents a fraction of one. For example, 12:00 PM is 0.5.

Excel converts a date to a number based on the number of days since January 1, 1900. For example, the date April 13, 1987, is represented as 31,880 because 31,880 days have passed since January 1, 1900.

This principle is the basis for calculating time differences. To find the number of days between two dates, subtract the earlier date from the later one.

Example of the DATE Function

This function constructs a date by combining separate elements: year, month, and day.

Syntax: year, month, day.

All arguments are required and can be specified as numbers or cell references containing the respective values. For year, use values between 1900 and 9999; for month, use 1 to 12; for day, use 1 to 31.

If the day argument exceeds the days in a specified month, the extra days carry over to the next month. For example, setting 32 days for December results in January 1 of the following year.

Example:

DATE Function Example

Setting a larger number of days for June:

More Days Example

Using cell references as arguments:

Cell Reference Example

The DATEDIF Function in Excel

This function returns the difference between two dates.

Arguments:

  • Start date;
  • End date;
  • Unit code (days, months, years, etc.).

Ways to measure intervals between specified dates:

  • For days, use “d”;
  • For months, use “m”;
  • For years, use “y”;
  • For months without years, use “ym”;
  • For days without months and years, use “md”;
  • For days without years, use “yd”.

In some Excel versions, the “md” and “yd” arguments may produce errors. It’s often better to use alternative formulas.

DATEDIF function examples:

DATEDIF Function Example

This function is not listed in Excel 2007’s library but still works, though results may need verification due to occasional inaccuracies.

The YEAR Function in Excel

This function returns the year as an integer (1900 to 9999) for a specified date. It has one argument: a date in numeric format, either from the DATE function or the result of another formula.

YEAR function example:

YEAR Function Example

MONTH Function in Excel: Example

This function returns the month as an integer (1 to 12) for a specified date in numeric format. It doesn’t handle text-formatted dates correctly.

MONTH function example:

MONTH Function Example

Examples of the DAY, WEEKDAY, and WEEKNUM Functions in Excel

The DAY function returns the day of the month as an integer (1 to 31) for a specified date.

DAY Function Example

To return the day of the week for a given date, use the WEEKDAY function:

WEEKDAY Function Example

By default, Sunday is the first day of the week.

To get the week number for a specific date, use the WEEKNUM function:

WEEKNUM Function Example

The date May 25, 2025, falls in week 22 of the year, with Sunday as the starting day.

Starting on Monday

Here, the second argument is set to 2, making Monday the start of the week.

Download examples of date functions in Excel download file

To display the current date, use the TODAY function (no arguments). For current date and time, use the NOW() function.