Excel Date Functions with Formula Examples

Excel date and time functions help you manage, calculate, and analyze dates efficiently for scheduling, reporting, and time-based tasks.

• Retrieve, convert, and format dates using functions like DATE, TODAY, NOW, TEXT, DAY, MONTH, and YEAR.
• Calculate date differences, workdays, week numbers, and month-end dates with functions such as DATEDIF, WORKDAY, NETWORKDAYS, WEEKNUM and EOMONTH.

List of Date Functions

We can use the following functions for various date tasks:

• Get Current Date and Time: TODAY, NOW
• Convert Dates to/From Text: DATEVALUE, TEXT
• Retrieve Dates: DAY, MONTH, YEAR, EOMONTH, WEEKDAY, WEEKNUM
• Calculate Date Difference: DATEDIF, EDATE, YEARFRAC
• Calculate Workdays: WORKDAY, WORKDAY.INTL, NETWORKDAYS, NETWORKDAYS.INTL

We will explore each function with examples and steps.

1. DATE Function

The DATE function returns a valid Excel date by combining the specified year, month, and day values.

Syntax

DATE(year,month,day)

Arguments

• Year: The year value to use in the date. Excel accepts year numbers and may interpret two-digit years according to its date system (for example, 25 may be interpreted as 2025).
• Month: This argument includes a positive or negative integer that represents the month of the year from January to December.
• Day: This argument also includes a positive or negative integer representing the day of the month from 1 to 31.

Example 1

Example 2

 It will return on the first day of the current year & month.

Example 3

2. TODAY Function

The TODAY() function name suggests it will return today's date and it has no arguments.

Syntax

TODAY()

Example 1

Here we will print the current date and also add 10 days to the current date.

Example 2

To add 10 days to Today's date.

3. NOW Function

This function returns the current date as well as the time & doesn't have any arguments.

Syntax

NOW()

Example 1

4. DATEVALUE Function

It converts the date in text format to a serial number, which can be represented as a date.

Syntax

DATEVALUE(date_text)

Arguments

• date_text - This argument is a text that represents the date in Excel date format.

Example 1

5. Excel TEXT Function

It converts any numeric value not only dates to a text string. Through this function, we can change the date to text strings in a variety of formats.

Syntax

TEXT(value,format_text)

Arguments

• value: The value that is to be converted.
• format_text: The format in which we want to output the date value.

These are the different formats used in the TEXT function to change dates to text strings.

Example 1

Example 2

Example 3

6. DAY Function

It returns the day of a month, i.e. integer from 1 to 31.

Syntax

DAY(serial_number)

Arguments

• serial_number: A valid Excel date (or cell reference containing a date) from which the day value is extracted.

Example 1

Example 2

The DAY(TODAY()) function returns the day of today's date, as shown below:

7. MONTH Function

This function returns the month of the given date as an integer from 1 to 12 (January to December).

Syntax

MONTH(serial_number)

Arguments

• serial_number: This value represents the date for which we want to find the month.

Example 1

The MONTH(TODAY()) function returns the month of today's date.

8. YEAR Function

It returns the year of a specified date.

Syntax

YEAR(serial_number)

Arguments

• serial_number: The date to be specified.

Example 1

Example 2

Example 3

9. EOMONTH Function

This function returns the last day of the month after adding a specified number of months to a given date.

Syntax

EOMONTH(start_date,months)

Arguments

• start_date: In this argument, the date should be written in date format, not in the text.
• months: In this argument, if a positive integer is given then corresponding months can be added to the start date & if a negative integer is given then the corresponding months can be subtracted from the start date.

Example 1

Example 2

Example 3

The EOMONTH(TODAY(),0) function returns the last day of the current month.

10. WEEKDAY Function

This function returns the day of the week as a number from 1 to 7 (Sunday to Saturday) according to the specified date.

Syntax

WEEKDAY(serial_number,return_type)

Arguments

• serial_number: It can be a date or the cell that contains the date.
• return_type: It is optional, as it specifies which day should be considered as the first day of the week.

Note: If the return_type argument is omitted, Excel uses 1 as the default value, where Sunday is treated as day 1 and Saturday as day 7.

Example 1

Example 2

In the below example, 2 is given as return_type i.e. Monday is referred to as the first day of the week.

Example 3

Here the day of today's (01-04-2021) date is the result & the default value (Sunday) is considered here because no return_type is given.

11. DATEDIF Function

This DATEDIF function calculates the difference between two dates in days, months, or years. For calculating the difference between dates which time interval should be used depends on the letter which we specify in our last argument i.e. at the unit.

Syntax

DATEDIF(start_date,end_date,unit)

Arguments

• start_date: The start date for evaluating the difference.
• end_date: The end date for evaluating the difference.

Example 1

Example 2

Example 3

Here "m", "y","d” mean month, year & date. In the first example, the difference between dates is calculated by months, second by year & third by date.

12. WEEKNUM Function

It returns the week number based on the specified date, i.e. from 1 to 52 weeks of the year.

Syntax

WEEKNUM(serial_number,firstday_ofweek)

Arguments

• serial_number: This is the date for which we want the week number.
• firstday_ofweek: This is an optional argument that specifies which numbering system should be considered & which day of the week can be treated as the start of the week, Default(omitted) is 1. The table below is the parameters that can be given in firstday_ofweek argument.

First Day of the Week Start Table

Example 1

Example 2

Example 3

In the below example,21 is given as the second argument which means Monday is taken as the first day of the week & in the above example, the result shown is 15 but taking 21 as the first_dayofweek means Monday is the first day, the result is 14.

13. EDATE Function

This function adds or subtracts the specified month to a given date.

Syntax

EDATE(start_date,months)

Arguments

• start_date: This is the initial date on which the months are added or subtracted.
• months: This is the number of months which is to be added or subtracted in the specified date.

Example 1

Example 2

Example 3

14. YEARFRAC Function

This function returns the fraction of the year which represents the number of whole days between the start and end date.

Syntax

YEARFRAC(start_date,end_date,[basis])

Arguments

• start_date: This is the start date in the serial number.
• end_date: This is the end date in the serial number.
• basis: This is the optional argument that specifies the day count method.

Example 1

Using some day count methods.

Example 2

Example 3

15. WORKDAY Function

Returns a date after adding or subtracting a specified number of working days.

Syntax

WORKDAY(start_date,days,holidays)

Arguments

• start_date: This argument is the date from which the counting of weekdays begins. Excel doesn't include start_date as a working day.
• days: This is the number of working days.
• holidays: This is an optional argument. If the days mentioned include any holidays, then we need to make a list of holidays separately for this and mention it here.

Example 1

28 workdays from the start date, excluding holidays.

Example 2

28 workdays before the start date, excluding holidays

Example 3

28 workdays from the start date, no holidays.

Example 4

28 workdays from today's date, no holidays.

16. WORKDAY.INTL Function

This is a modification of the WORKDAY function as it provides a custom weekend parameter that distinguishes this from the WORKDAY function.

Syntax

WORKDAY.INTL(start_date,days,[weekends],holidays)

Arguments

• start_date: This argument is the date from which the counting of weekdays begins. Excel doesn't include start_date as a working day.
• days: This is the number of working days.
• holidays: This is an optional argument. If the days mentioned include any holidays, then we need to make a list of holidays separately for this and mention it here.
• weekends: Through this argument, we can specify which days of the week to be treated as non-working days, either by weekend number or specific character string.

Weekend Number

If this weekend argument is blank in this function, then it will automatically take the combination of Saturday & Sunday.

For instance:

• "0000011"-Saturday & Sunday are weekends(non-working days)
• "1000010"-Monday & Saturday are weekends(non-working days)

Example 1

30 days from the start date, excluding holidays & Sundays and Monday as weekends (by giving weekend number 2 as arguments).

Example 2

30 days from the start date, excluding holidays & Sunday, Monday as weekends(by giving weekend string "1000001" as arguments).

Example 3

20 days from the start date, no holidays & Monday and Saturday as weekends (by giving the weekend string "1000010" as arguments).

17. NETWORKDAYS Function

This function returns the number of working days between two dates, excluding weekends & holidays as optional arguments.

Syntax

NETWORKDAYS(start_date,end_date,holidays)

Arguments

• start_date: The initial date to start the evaluation.
• end_date: The last date to end the evaluation.
• holidays: Used to specify holidays.

Example 1

Example 2

18. NETWORKDAYS.INTL Function

This function also returns the number of working days between two dates but provides the additional argument weekend to specify which days should be counted as weekend days.

The structure of the weekend argument is the same as for WORKDAY.INTL i.e. we can use either the weekend number or character string.

Syntax

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

Arguments

• start_date: The initial date to start the evaluation.
• end_date: The last date to end the evaluation.
• weekend: Use to specify the weekends.
• holidays: Used to specify holidays.

Example 1

Here, the weekend argument is given in the form of a number.

Example 2

Here, the weekend argument is given in the form of a character string of 0's and 1's.