Date and Time functions in Excel help you to simplify handling 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 some of the time functions in Excel. If you’re looking for date functions, you can check out the top 8 date functions that we covered recently.
1. HOUR function
The hour of a time value is returned by the HOUR function. The hour is represented as an integer, with values ranging from 0 to 23.
Syntax: HOUR(time), where time includes the hour you’re looking for. The following example shows the use of HOUR function:
- Select the cell where you want to display the result.
- Type =HOUR(A2), where cell A2 contains the time.
- Press the Enter key to display the result.
2. ISOWEEKNUM function
For a given date, the ISOWEEKNUM function returns the ISO week number of the year.
Syntax: ISOWEEKNUM(date), where date is the date-time code that Excel uses to calculate dates and times. The following example shows the use of ISOWEEKNUM function:
- Select the cell where you want to display the result.
- Type =ISOWEEKNUM(A2), where cell A2 contains the date.
- Press the Enter key to display the result.
Note: This function is available in Excel 2013 and above.
3. MINUTE function
The minutes of a time value is returned by the MINUTE function. The minute is expressed as an integer between 0 and 59.
Syntax: MINUTE(time), where time includes the hour you’re looking for. The following example shows the use of MINUTE function:
- Select the cell where you want to display the result.
- Type =MINUTE(A2), where cell A2 contains the time.
- Press the Enter key to display the result.
4. MONTH function
The month of a date is returned by the MONTH function. The month is specified as an integer number ranging from 1 for January to 12 for December.
Syntax: MONTH(date), where date is the date-time code that Excel uses to calculate dates and times. The following example shows the use of MONTH function:
- Select the cell where you want to display the result.
- Type =MONTH(A2), where cell A2 contains the date.
- Press the Enter key to display the result.
5. NETWORKDAYS function
The NETWORKDAYS function returns the total number of working days between the start date and the end date. Weekends and holidays are not considered working days. Use NETWORKDAYS to compute employee benefits based on the number of days worked over a certain term.
Syntax: NETWORKDAYS(start_date, end_date, [holidays]), where start_date and end_date are the first and last day of the period and holidays parameter to include dates to be excluded. the following example shows the use of NETWORKDAYS function:
- Select the cell where you want to display the result.
- Type =NETWORKDAYS(A2,B2,C2), where cells A2, B2 and C2 contains the start date. end date and holiday respectively.
- Press the Enter key to display the result.
6. NETWORKDAYS.INTL
The NETWORKDAYS.INTL returns the number of entire workdays between two dates, with arguments specifying which and how many days are weekends. Weekends and other days designated as holidays are not considered workdays.
Syntax: NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]), where start_date and end_date are the first and last day of the period, 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 NETWORKDAYS.INTL function:
- Select the cell where you want to display the result.
- Type =NETWORKDAYS(A2,B2,C2,D2), where cells A2, B2 and C2 contains the start date. end date, weekend, and holidays respectively.
- Press the Enter key to display the result.
Note: This function is available in Excel 2010 and above.
7. NOW function
The NOW function returns the current system date and time. The value is updated every time the worksheet is opened or recalculated.
Syntax: NOW() and it has no arguments. The following example shows the use of NOW function.
- Select the cell where you want to display the result.
- Type =NOW().
- Press the Enter key to display the result.
8. SECOND function
The seconds of a time value are returned by the SECOND function. The second is expressed as an integer between 0 and 59.
Syntax: SECOND(time), where time includes the seconds you’re looking for. The following example shows the use of SECOND function:
- Select the cell where you want to display the result.
- Type =SECOND(A2), where cell A2 contains the time.
- Press the Enter key to display the result.
Note: Dates are stored as consecutive serial numbers in Microsoft Excel so that they can be used in calculations. January 1, 1900, is serial number 1 by default, and January 1, 2008, is serial number 39448 because it is 39,448 days after January 1, 1900.
Conclusion
In this article, we learned some of the date and time functions in Excel.