Date and Time functions in Excel help you to simplify handling date and time operations easily. In this article, we’ll learn some of the date and time functions in Excel.
Date and Time Functions in Excel
Let’s get started with the date and time functions that you must know in Excel. If you haven’t read through the part 1, you can check it out here.
Top 8 Date and Time Functions in Excel – A Quick Reference [Part 1]
1. TIME function
The decimal number for a specific time is returned by the TIME function. TIME returns a decimal number ranging from 0 (zero) to 0.99988426, which represents times from 0:00:00 to 23:59:59.
Syntax: TIME(hour, minute, second), where hour is the hour value from 0 to 23, a minute is the minute value from 0 to 59, and second is the second value from 0 to 59. The following example shows the use of the TIME function:
- Select the cell where you want to display the result.
- Type =TIME(A2,B2,C2), where cells A2, B2, and C2 contain the hour, minute, and seconds value respectively.
- Press the Enter key to display the result.
2. TIMEVALUE function
The decimal number for a specific time represented by a text string is returned by the TIMEVALUE function. The function returns a decimal number ranging from 0 (zero) to 0.99988426, which represents times from 0:00:00 to 23:59:59.
Syntax: TIMEVALUE(time_text), where time_text is a text string representing time. The following example shows the use of TIMEVALUE function:
- Select the cell where you want to display the result.
- Type =TIMEVALUE(“13:10”).
- Press the Enter key to display the result.
3. TODAY function
The TODAY function returns today’s date. This function is handy when you require the current date displayed on a worksheet whenever the workbook is opened or recalculated. It can also be used to calculate intervals.
Syntax: TODAY() and it takes no arguments. The following example shows the use of the TODAY function:
- Select the cell where you want to display the result.
- Type =TODAY().
- Press the Enter key to display the result.
4. WEEKDAY function
The day of the week matching to date is returned by the WEEKDAY function. By default, the day is specified as an integer ranging from 1 (Sunday) to 7 (Saturday). Syntax: WEEKDAY(date), where a date is whose day of the week is to be found out. The following example shows the use of the WEEKDAY function:
- Select the cell where you want to display the result.
- Type =WEEKDAY(A2), where cell A2 contains the date.
- Press the Enter key to display the result. The function returns 1 which implies 9/26/2021 is Sunday.
5. WEEKNUM function
The week number of a given date is returned by the WEEKNUM function. The week beginning on January 1st is the first week of a given year and is designated as week 1.
Syntax: WEEKNUM(date), where the date is whose week number we want to find. The following example shows the use of the WEEKNUM function:
- Select the cell where you want to display the result.
- Type =WEEKNUM(A2), where cell A2 contains the date.
- Press the Enter key to display the result.
6. WORKDAY function
The WORKDAY function returns a date that is the specified number of working days before or after a given date. Weekends and other dates designated as holidays are not considered working days. When calculating invoice due dates, projected delivery schedules, or the number of days of work accomplished, use WORKDAY to omit weekends and holidays.
Syntax: WORKDAY(start_date, days, [holidays]), where start_date is the first day of the period, days is the number of non-weekend days, and holidays parameter to include dates to be excluded. The following example shows the use of WORKDAY function:
- Select the cell where you want to display the result.
- Type =WORKDAY(A2,B2), where cells A2 and B2 contain the start date and number of days to completion respectively.
- Press the Enter key to display the result.
7. WORKDAY.INTL function
The WORKDAY.INTL function returns the date before or after a specified number of workdays with custom weekend parameters. Weekend settings specify which and how many days are designated as weekends. Weekends and other days designated as holidays are not considered workdays.
Syntax: WORKDAY.INTL(start_date, days, [weekend], [holidays]), where start_date is the first and last day of the period, days is the number of non-weekend days, holidays parameter to include dates to be excluded and weekend indicates the weekend days. Weekend number values are as follows:
- 1 or omitted: Saturday & Sunday,
- 2: Sunday & Monday,
- 3: Monday & Tuesday,
- 4: Tuesday & Wednesday,
- 5: Wednesday & Thursday,
- 6: Thursday & Friday,
- 7: Friday & Saturday,
- 11: Sunday only,
- 12: Monday only,
- 13: Tuesday only,
- 14: Wednesday,
- 15: Thursday only,
- 16: Friday only,
- 17: Saturday only.
The following example shows the use of WORKDAY.INTL function:
- Select the cell where you want to display the result.
- Type =WORKDAY.INTL(A2,B2,C2), where cells A2, B2, and C2 contain the start date, number of days to completion, and weekend parameter respectively.
- Press the Enter key to display the result.
8. YEAR function
The YEAR returns the year that corresponds to a given date. The year is returned as an integer between 1900 and 9999.
Syntax: YEAR(date), where date is whose year you want to find out. The following example shows you the use of YEAR function:
- Select the cell where you want to display the result.
- Type =YEAR(A2), where cell A2 contains the date.
- Press the Enter key to display the result.
9. YEARFRAC function
YEARFRAC function calculates the percentage of a year represented by the number of entire days between the start date and the end date.
Syntax: YEARFRAC(start_date, end_date, [basis]). The day count basis 0 or omitted: US (NASD) 30/360, 1: Actual/Actual, 2: Actual/360, 4: Actual/365, and 5: European 30/360. The following example shows you the use of YEARFRAC function:
- Select the cell where you want to display the result.
- Type =YEARFRAC(A2,B2,1), where cells A2 and B2 contain the start date and the end date respectively.
- Press the Enter key to display the result.
Conclusion
In this article, we learned some of the date and time functions in Excel.