In this article, we’ll take you on a journey through the diverse range of powerful Excel date functions. From simple functions like DATE, TODAY, and NOW, to more advanced functions like EDATE, WORKDAY, and NETWORKDAYS.INTL. We’ll explore each function’s syntax, usage, and practical examples.
Whether you’re a data analyst, project manager, or finance professional, understanding how to leverage Excel date functions can significantly enhance your productivity. By the end of this article, you’ll be equipped with the knowledge to effortlessly calculate durations, extract date components, and manipulate dates and time in Excel like a pro.
Download Practice Workbook
Download this workbook to better understand.
21 Excel Date Functions to Work with
1. Insert Dates Using DATE Function
The DATE function in Excel is used to create a date based on the provided year, month, and day.
Syntax of DATE function:
You can use value or cell reference to assign year, month and day in the arguments.
Usually, the result of the function will be the serial number for that date. So you need to format your cell to display the date according to your preference. Press Ctrl+1 to change format.
2. Get Current Date
2.1 Using TODAY Function
The TODAY function in Excel is used to return the current date as a serial number.
Syntax of TODAY function:
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. To display date in your desired format, press Ctrl+1 to access Format Cells.
2.2 Applying NOW Function
The NOW function in Excel returns the current date and time as a serial number.
Syntax of NOW function:
When you use the NOW function in a cell, it will automatically update to the current date and time whenever you open or recalculate the Excel workbook. The difference between these two functions is that TODAY only returns the current date whereas NOW returns both date and time. You can format the cell to show only the date.
3. Change Dates from/to Text
3.1 Using DATEVALUE Function
The DATEVALUE function in Excel converts a date represented as text into a serial number that Excel recognizes as a date.
Syntax of DATEVALUE function:
Dates are stored in Excel as consecutive integers, and it is only the formatting of a cell that permits a number to be shown as a date. All dates are recorded as integers denoting the number of days from January 1, 1900 (number 1) to December 31, 9999 (number 2958465). 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 TEXT function:
In the format_text argument type your preferred formatting.
4. Fetch Dates
4.1 Using DAY Function
The DAY function extracts the day of the month from a given date and returns it as a numeric value.
Syntax of DAY function:
4.2 Using MONTH Function
The MONTH function extracts the month from a given date and returns it as a numeric value.
Syntax of MONTH function:
4.3 Applying YEAR Funcion
The YEAR function extracts the month from a given date and returns it as a numeric value.
Syntax of YEAR function:
4.4 Utilizing 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 EOMONTH function:
Suppose, your start date is January 5, and you want to add 3 months. So the function result will be April 30.
4.5 Applying WEEKDAY Function
The WEEKDAY function returns the day of the week for a given date and returns it as a numeric value.
Syntax of WEEKDAY function:
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 insert as the return_type will determine which date should be considered as the first day of the week.
4.6 Using WEEKNUM Function
The WEEKNUM function calculates the week number of a given date based on a specified numbering system.
Syntax of WEEKNUM function:
The WEEKNUM function can be used in two different ways based on return type:
Way 1: Week 1 specifies the week which contains January 1st;
Way 2: Week 1 is the week that contains the first Thursday of the year.
4.7 Utilizing ISOWEEKNUM Function
The ISOWEEKNUM function gives the ISO week number of the year of a given date.
Syntax of ISOWEEKNUM function:
The ISO week numbering system considers the week containing the first Thursday of the year as the first week of the year, and each week starts on Monday. ISO week numbers can vary from 1 to 53.
5. Calculate Date Difference
5.1 Using DATEDIF Function
The DATEDIF function calculates the difference between two dates in terms of years, months, or days.
Syntax of DATEDIF function:
Units of this function are as follows.
| 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 DAYS Function
The DAYS function gives the number of days between two dates.
Syntax of DAYS function:
5.3 Applying DAYS360 Function
The DAYS360 function calculates the difference between two dates using a 360-day year, assuming each month has 30 days.
Syntax of DAYS360 function:
Method is an optional argument to specify the day-count method. TRUE means the function counts European method and FALSE means the method will be the US method. The default is FALSE.
5.4 Applying EDATE Function
The EDATE function adds or subtracts a specified number of months to a given date and returns the resulting date.
Syntax of EDATE function:
5.5 Utilizing YEARFRAC Function
The YEARFRAC function calculates the fraction of a year between two dates.
Syntax of YEARFRAC function:
6. Calculate Workdays
6.1 Using WORKDAY Function
The WORKDAY function calculates a future or past date by skipping weekends and specified holidays. The specified number of days to be added or subtracted is to given by the user.
Syntax of WORKDAY function:
The function calculates only working days and excludes weekends and holidays. If there is any extra holiday for your specific time zone, you need to specify them in the holidays argument.
6.2 Applying WORKDAY.INTL Function
The WORKDAY.INTL function calculates a future or past date by excluding specific weekends and holidays.
Syntax of WORKDAY.INTL function:
WORKDAY and WORKDAY.INTL functions both do the same thing. The only difference between them is that WORKDAY.INTL function lets users choose which days to be considered as weekends.
6.3 Using NETWORKDAYS Function
The NETWORKDAYS function calculates the number of working days excluding weekends and specified holidays between two dates.
Syntax of NETWORKDAYS function:
6.4 Using NETWORKDAYS.INTL Function
The last one among many Excel date functions is the NETWORKDAYS.INTL function. This function calculates the number of working days excluding weekends and specified holidays between two dates using a custom weekend parameter.
Syntax of NETWORKDAYS.INTL function:
NETWORKDAYS and NETWORKDAYS.INTL functions both do the same thing. The only difference between them is that NETWORKDAYS.INTL function lets users choose which days to be considered as weekends.
Get to Know Excel Time Functions
1. HOUR, MINUTE & SECOND Functions
The HOUR, MINUTE, and SECOND functions are used to extract the hour, minute, and second components from a given time, respectively.
Syntax of HOUR function:
Syntax of MINUTE function:
Syntax of SECOND function:
2. TIME Function
The TIME function creates a time value based on the provided hour, minute, and second components.
Syntax of TIME function:
3. TIMEVALUE Function
The TIMEVALUE function converts a time string (formatted as text) into a time value, which Excel recognizes as a numeric representation of time.
Syntax of TIMEVALUE function:
Frequently Asked Questions
1. How do I calculate someone’s age based on their birthdate?
Ans: To calculate someone’s age based on their birthdate in Excel, use the DATEDIF function. The formula looks like this:
=DATEDIF(Birthdate, TODAY(), "y")Replace Birthdate with the cell reference containing the birth date. The formula will return the person’s age in years as of the current date (TODAY()).
2. How do I convert a date to a different date format?
Ans: You can convert a date to a different date format in Excel.
- Select the cell with the date.
- Go to the Home tab, and click the drop-down arrow in the Number Format box.
- Choose More Number Formats and then select the desired date format from the list or create a custom format.
The date will be displayed in the chosen format.
3. How do I work with dates and time in different time zones?
Ans: To work with dates and times in different time zones in Excel. First, ensure your dates/times are in a standardized format (e.g., UTC). Then, use the CONVERT function to adjust the time zone offset. For example:
=CONVERT(Date_Time, "UTC", "Time_Zone")Replace Date_Time with the cell reference containing the date and time, and Time_Zone with the desired time zone code (e.g., -07:00 for Pacific Time).
Conclusion
In conclusion, Excel date functions can save your time, eliminate human errors, and elevate your data analysis skills to a whole new level. Having skill over functions like DATE, TODAY, NOW, DATEVALUE allows you to perform simple yet critical date-related calculations effortlessly. Additionally, you’ve explored more advanced functions such as EDATE and WORKDAY, enabling you to handle complex scenarios like date manipulations, workday calculations, and custom weekend settings with ease. Thank you for embarking on this journey with us, and we wish you success in all your future Excel endeavors!
<< Go Back to Excel Function Categories | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
