# Excel Date Functions – A Complete Guide

This is an overview.

.

Download Practice Workbook

Download the workbook here.

### 1 – Insert Dates Using the DATE Function

The DATE functionÂ is used to create a date based on the provided year, month, and day.

Syntax of DATE function:

=DATE(year, month, day)

Use a value or cell reference to assign year, month and day in the arguments.

Note:
The result of the function will be the serial number for that date. Format your cell to display the date: Press Ctrl+1 to change format.

### 2 – Getting the Current Date

#### 2.1 Using the TODAY Function

The TODAY functionÂ is used to return the current date as a serial number.

Syntax of TODAY function:

=TODAY()

When you use the TODAY function in a cell, it will automatically update to the current date whenever you open or recalculate the Excel workbook. Format the date: press Ctrl+1 to access Format Cells.

#### 2.2 Applying the NOW Function

The NOW function returns the current date and time as a serial number.

Syntax of the NOW function:

=NOW()

The NOW function automatically updates to the current date and time whenever you open or recalculate the Excel workbook. TODAY only returns the current date, whereas NOW returns both date and time.

### 3 – Change Dates from/to Text

#### 3.1 Using the DATEVALUE Function

The DATEVALUE function converts a date represented as text into a serial number.

Syntax of the DATEVALUE function:

=DATEVALUE(date_text)

Note:
Dates are stored in Excel as consecutive integers. Only the formatting permits a number to be shown as a date. You can change the serial number back to date format by pressing Ctrl+1.

#### 3.2 Applying TEXT Function

The TEXT function is used to convert a numeric value, date, or time into a text string with a specified format.

Syntax of the TEXT function:

=TEXT(value, format_text)

In the format_text argument, choose your formatting.

### 4 – Get Dates

#### 4.1 Using the DAY Function

The DAY function extracts the day of the month from a given date and returns it as a numeric value.

Syntax of the DAY function:

=DAY(date)

#### 4.2 Using the MONTH Function

The MONTH function extracts the month from a given date and returns it as a numeric value.

Syntax of the MONTH function:

=MONTH(date)

#### 4.3 Applying the YEAR Funcion

The YEAR function extracts the year from a given date and returns it as a numeric value.

Syntax of the YEAR function:

=YEAR(date)

#### 4.4 Utilizing the EOMONTH Function

The EOMONTH function finds the last day of the output month based on a specified number of months before or after a referenced date.

Syntax of the EOMONTH function:

=EOMONTH(start_date, months)

The start date is January 5, and you want to add 3 months. The function returns April 30.

#### 4.5 Applying the WEEKDAY Function

The WEEKDAY function returns the day of the week for a given date as a numeric value.

Syntax of the WEEKDAY function:

=WEEKDAY(date, [return_type])

Note:
There is an optional argument to specify the numbering system for the days of the week. By default, it considers Sunday as the first day of the week, represented by 1, and Saturday as the last day of the week, represented by 7. The number you enter as the return_type will determine which date should be considered the first day of the week.

#### 4.6 Using the WEEKNUM Function

The WEEKNUM function calculates the week number of a given date based on a specified numbering system.

Syntax of the WEEKNUM function:

=WEEKNUM(date, [return_type])

The WEEKNUM function can return:

Â 1: Week 1 specifies the week which contains January 1st;

Â 2: Week 1 is the week that contains the first Thursday of the year.

#### 4.7 Utilizing the ISOWEEKNUM Function

The ISOWEEKNUM function gives the ISO week number of the year of a given date.

Syntax of the ISOWEEKNUM function:

=ISOWEEKNUM(date)

Note:
The ISO week numbering system considers the week containing the first Thursday of the year as the first week of the year. Each week starts on Monday. ISO week numbers can vary from 1 to 53.

### 5 – Calculate Date Difference

#### 5.1 Using the DATEDIF Function

The DATEDIF function calculates the difference between two dates in terms of years, months, or days.

Syntax of the DATEDIF function:

=DATEDIF(start_date, end_date, unit)

Units in this function are:

Unit Difference Type
D Days between two dates.
M Months between two dates.
Y Years between two dates.
MD Days between two dates ignoring months and years.
YM Months between two dates ignoring years.
YD Days between two dates ignoring years.

#### 5.2 Using the DAYS Function

The DAYS function gives the number of days between two dates.

Syntax of the DAYS function:

=DAYS(end_date, start_date)

#### 5.3 Applying the DAYS360 Function

The DAYS360 function calculates the difference between two dates using a 360-day year, assuming each month has 30 days.

Syntax of the DAYS360 function:

=DAYS360(start_date, end_date, [method])

Note:
Method is an optional argument that specifies the day-count. TRUE is the European method and FALSE is the US method. Default is FALSE.

#### 5.4 Applying the EDATE Function

The EDATE function adds or subtracts a specified number of months from a given date and returns the resulting date.

Syntax of the EDATE function:

=EDATE(start_date, months)

#### 5.5 Utilizing the YEARFRAC Function

The YEARFRAC function calculates the fraction of a year between two dates.

Syntax of the YEARFRAC function:

=YEARFRAC(start_date, end_date, [basis])

### 6 – Calculate Workdays

#### 6.1 Using the WORKDAY Function

The WORKDAY function calculates a future or past date by skipping weekends and specified holidays.

Syntax of the WORKDAY function:

=WORKDAY(start_date, days, [holidays])

Notes:
If there is a holiday in your time zone, you need to specify it in the holidays argument.

#### 6.2 Applying the WORKDAY.INTL Function

The WORKDAY.INTL function calculates a future or past date by excluding specific weekends and holidays.

Syntax of the WORKDAY.INTL function:

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

Note:
Â TheÂ  WORKDAY.INTL function allows users to choose which days are considered as weekend.

#### 6.3 Using the NETWORKDAYS Function

The NETWORKDAYS function calculates the number of working days excluding weekends and specified holidays between two dates.

Syntax of the NETWORKDAYS function:

=NETWORKDAYS(start_date, end_date, [holidays])

#### 6.4 Using the NETWORKDAYS.INTL Function

The NETWORKDAYS.INTL function calculates the number of working days excluding weekends and specified holidays between two dates using a custom weekend parameter.

Syntax of the NETWORKDAYS.INTL function:

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

Note:
The NETWORKDAYS.INTL function allows users to choose which days are considered as weekend.

## Excel Time Functions

### 1 – HOUR, MINUTE & SECOND Functions

The HOUR, MINUTE, and SECOND functions are used to extract the hour, minute, and second from a given time.

Syntax of the HOUR function:

=HOUR(time)

Syntax of the MINUTE function:

=MINUTE(time)

Syntax of the SECOND function:

=SECOND(time)

### 2 – TIME Function

The TIME function creates a time value based on the provided hour, minute, and second.

Syntax of the TIME function:

=TIME(hour, minute, second)

### 3. TIMEVALUE Function

The TIMEVALUE function converts a time string (formatted as text) into a time value, a numeric representation of time.

Syntax of the TIMEVALUE function:

=TIMEVALUE(hour, minute, second)

## Frequently Asked Questions

1. How do I calculate someone’s age based on their birthdate?
Ans: Use the DATEDIF function:

`=DATEDIF(Birthdate, TODAY(), "y")`

Replace Birthdate with the cell reference containing the birth date. The formula will return the person’s current age in years.

2. How do I convert a date to a different date format?
Ans: To convert a date to a different date format:

• Select the cell with the date.
• Go to the Home tab, and click Number Format.
• Choose More Number Formats and select a date format or create a custom format.

3. How do I work with dates and time in different time zones?
Ans: Ensure your dates/times are in a standardized format (e.g., UTC). Use the CONVERT function to adjust the time zone offset:

`=CONVERT(Date_Time, "UTC", "Time_Zone")`

Replace Date_Time with the cell reference containing the date and time, and Time_Zone with the time zone code (e.g., -07:00 for Pacific Time).

<< Go Back to Excel Function Categories | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hassan

Mehedi Hassan, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, functions as an Excel & VBA Content Developer at ExcelDemy. His deep interest in research and innovation aligns seamlessly with his fervor for Excel. In this role, Mehedi not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, emphasizing his unwavering dedication to consistently delivering outstanding content. His interests are Advanced... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF