Chhaya Wagmi

# Working with Time and Date Functions in Excel - Part 2

• Sep 14, 2020
• 1,988 Views
• Sep 14, 2020
• 1,988 Views
Microsoft Excel

## Introduction

This guide is in continuation of the Date and Time Function in Excel - Part 1. In this guide, you will learn how to work with the rest of the time and date functions in Microsoft Excel 2019. Here's the list of functions that you are going to learn:

• NETWORKDAYS
• NETWORKDAYS.INTL
• NOW
• SECOND
• TIME
• TIMEVALUE
• TODAY
• WEEKDAY
• WEEKNUM
• WORKDAY
• WORKDAY.INTL
• YEAR
• YEARFRAC

## The NETWORKDAYS Function

The `NETWORKDAYS` function gives "the total number of workdays if you provide a start date and an end date. It excludes weekends and dates that are identified as holidays while returning the output," according to Excel's documentation.

The `NETWORKDAYS` function has the following syntax:

``1=NETWORKDAYS(old_date, new_date, [optional_holidays])``

The arguments `old_date` and `new_date` are required arguments in the function and represent the start date and the end date. The third argument in the function is optional. To implement the `NETWORKDAYS` function in Excel, consider the example given below:

ABCDE
Sr. No.Start_DateEnd_DateHolidayWorkDays
123-Jul-1930-Jul-19=NETWORKDAYS(B1,C1)
201-Jan-1931-Jan-201926-Jan-2019=NETWORKDAYS(B2,C2,D2)
324-Jul-1923-Aug-201915-Aug-2019=NETWORKDAYS(B3,C3,D3)

The result will be updated as shown below:

ABCDE
Sr. No.Start_DateEnd_DateHolidayWorkDays
123-Jul-1930-Jul-196
201-Jan-1931-Jan-201926-Jan-201923
324-Jul-1923-Aug-201915-Aug-201922

## The NETWORKDAYS.INTL Function

The `NETWORKDAYS.INTL` function is very similar to the `NETWORKDAYS` function. This function also gives "the total number of workdays between two dates but it also has a parameter that provides you the option to select the day(s) that you want to select as the weekend which makes it different than the `NETWORKDAYS` function," according to Excel's documentation.

The `NETWORKDAYS.INTL` function has the following syntax:

``1=NETWORKDAYS.INTL(old_date, new_date, [opt_weekend], [opt_holidays])``

The first argument, `old_date`, and the second argument, `new_date`, are the required arguments in the function whereas the third argument, `opt_weekend`, and the fourth argument, `opt_holidays`, are optional.

To implement the `NETWORKDAYS.INTL` function in Excel, consider the example given below:

ABCDEF
Sr. No.Start_DateEnd_DateWeekendHolidayWorkDays
123-Jul-1930-Jul-191=NETWORKDAYS.INTL(B1,C1,D1)
201-Jan-1931-Jan-2019226-Jan-2019=NETWORKDAYS.INTL(B2,C2,D2,E2)
324-Jul-1923-Aug-2019315-Aug-2019=NETWORKDAYS.INTL(B3,C3,D3,E3)

The result will be updated as shown below:

ABCDEF
Sr. No.Start_DateEnd_DateWeekendHolidayWorkDays
123-Jul-1930-Jul-1916
201-Jan-1931-Jan-2019226-Jan-201922
324-Jul-1923-Aug-2019315-Aug-201922

## The NOW Function

The `NOW` function provides you with "the serial number for the current date and time. If the cell format is `General`, the output of the function is adjusted as per your regional date and time setting," according to Excel's documentation.

The `NOW` function has the following syntax with no arguments:

``1=NOW()``

In the table given below, you can understand the working of the `NOW` function:

FUNCTIONOUTPUTDETAILS
=NOW()25-07-2019 23:54return current date and time
=NOW() + 227-07-2019 23:54return date and time two days in future
=NOW() - 0.527-07-2019 11:54Returns the date and time 12 hours ago (-0.5 days ago)

## The SECOND Function

The `SECOND` function returns "seconds of a given time value/serial number and it ranges between 0-59," according to Excel's documentation.

The `SECOND` function has the following syntax:

``1=SECOND(time_value)``

To implement the `SECOND` function in Excel, consider the example given below:

ABC
Sr. No.TimeFunction
115:45:20 PM=SECOND(B1)
216:28 PM=SECOND(B2)
312:20:30 PM=SECOND(B3)

The result will be updated as shown below:

ABC
Sr. No.TimeFunction
115:45:20 PM20
216:28 PM0
312:20:30 PM30

## The TIME Function

The `TIME` function returns "the serial number for a given time and it ranges from 0 (zero) to 0.99988426, which represents the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.)," according to Excel's documentation.

The `TIME` function has the following syntax:

``1=TIME(hour, minute, second)``

To implement the `TIME` function in Excel, consider the example given below:

ABCDE
Sr. No.HourMinuteSecondOutput
1123045=TIME(B1,C1,D1)
21200=TIME(B2,C2,D2)
3142434=TIME(B3,C3,D3)

The result will be updated as shown below:

ABCDE
Sr. No.HourMinuteSecondOutput
11230450.521354167
212000.5
31424340.600393519

## The TIMEVALUE Function

The `TIMEVALUE` function returns "the serial number of the text formatted time and ranges from 0 (zero) to 0.99988426, which represents times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.)," according to Excel's documentation.

The `TIMEVALUE` function has the following syntax:

``1=TIMEVALUE(time_in_text_format)``

To implement the `TIMEVALUE` function in Excel, consider the example given below:

ABC
Sr. No.FunctionOutput
1=TIMEVALUE("01:30 AM")?
2=TIMEVALUE("04:30 AM")?
3=TIMEVALUE("04:30 PM")?

The result will be updated as shown below:

ABC
Sr. No.FunctionOutput
1=TIMEVALUE("01:30 AM")0.0625
2=TIMEVALUE("04:30 AM")0.1875
3=TIMEVALUE("04:30 PM")0.6875

### The TODAY Function

The `TODAY` function returns "the current date, if the cell was formatted as `General` before writing the function in it. If the cell is formatted as `Number`, it will return a serial number," according to Excel's documentation.

The `TODAY` function has the following syntax and has no arguments:

``1=TODAY()``

To implement the `TODAY` function in Excel, consider the example given below:

ABC
Sr. No.FunctionOutput
1=TODAY()?
3=TODAY() -30?

The result will be updated as shown below:

ABC
Sr. No.FunctionOutput
1=TODAY()26-07-2019
3=TODAY() - 3026-06-2019

## The WEEKDAY Function

The `WEEKDAY` function returns "the day of the week for a given date as input. The day of the week is returned as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default," according to Excel's documentation.

The `WEEKDAY` function has the following syntax:

``1=WEEKDAY(date,[response_type])``

The first argument in the function is required, whereas the second argument is optional. You can learn about the response type on this Excel page.

To implement the `WEEKDAY` function in Excel, consider the example given below:

ABCD
Sr. No.DateFunctionOutput
126-Jul-19=weekday(B1)?
226-Jul-19=weekday(B2,2)?
326-Jul-19=weekday(B3,3)?

The result will be updated as shown below:

ABCD
Sr. No.DateFunctionOutput
126-Jul-19=weekday(B1)6
226-Jul-19=weekday(B2,2)5
326-Jul-19=weekday(B3,3)4

## The WEEKNUM Function

The `WEEKNUM` returns "the week number of the given date," according to Excel's documentation. The input provided to the function must be in date format else it will cause an error.

The `WEEKNUM` function has the following syntax:

``1=WEEKNUM(date,[response_type])``

The first argument in the function is required whereas the second argument is optional. You can learn more about the second argument on this Excel page.

To implement the `WEEKNUM` function in Excel, consider the example given below:

ABCD
Sr. No.DateFunctionOutput
126-Jul-19=weeknum(B1)?
226-Jul-19=weeknum(B2,2)?
326-Jul-19=weeknum(B3,13)?

The result will be updated as shown below:

ABCD
Sr. No.DateFunctionOutput
126-Jul-19=weeknum(B1)30
226-Jul-19=weeknum(B2,2)30
326-Jul-19=weeknum(B3,13)31

### The WORKDAY Function

The `WORKDAY` function "returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Also, working days exclude all weekends and any dates which are identified as holidays," according to Excel's documentation.

The `WORKDAY` function has the following syntax:

``1=WORKDAY(starting_date, days, [opt_holidays])``

The first and the second argument are required, whereas the third argument is optional. To implement the `WORKDAY` function in Excel, consider the example given below:

ABCDE
Sr. No.DateWorking_DaysHolidayOUTPUT
126-07-20193015-08-2019=WORKDAY(B1,C1,D1)
226-07-20196016-08-2019=WORKDAY(B2,C2,D2)
326-07-20199017-08-2019=WORKDAY(B3,C3,D3)

The result will be updated as shown below:

ABCDE
Sr. No.DateWorking_DaysHolidayOUTPUT
126-07-20193015-08-201909-09-2019
226-07-20196016-08-201921-10-2019
326-07-20199017-08-201929-11-2019

### The WORKDAY.INTL Function

The `WORKDAY.INTL` function returns "the serial number of the date/date before or after a specified number of workdays along with the custom weekend parameters. The weekend parameters are used to indicate which and how many days are to be considered as weekend days. Weekend days and any other days that are specified as holidays are not considered as workdays," according to Excel's documentation.

The `WORKDAY.INTL` function has the following syntax:

``1=WORKDAY.INTL(date, days, [opt_weekend], [opt_holidays])``

The first and second arguments are required. The third argument and the fourth argument are optional. To implement the `WORKDAY.INTL` function in Excel, consider the example given below:

ABCDEF
SR. NO.DATEWORKING_DAYSHOLIDAYWEEKEND_NUMBER_VALUESOUTPUT
126-07-20193015-08-20192=WORKDAY.INTL(B1,C1,E1,D1)
226-07-20196016-08-20193=WORKDAY.INTL(B2,C2,E2,D2)
326-07-20199017-08-20197=WORKDAY.INTL(B3,C3,E3,D3)

The result will be updated as shown below:

ABCDEF
SR. NO.DATEWORKING_DAYSHOLIDAYWEEKEND_NUMBER_VALUESOUTPUT
126-07-20193015-08-2019207-09-2019
226-07-20196016-08-2019319-10-2019
326-07-20199017-08-2019728-11-2019

## The YEAR Function

The `YEAR` function returns "the year corresponding to a date and has a range 1900-9999," according to Excel's documentation.

The `YEAR` function has the following syntax:

``1=YEAR(date)``

To implement the `YEAR` function in Excel, consider the example given below:

ABCD
Sr. No.DateFunctionOutput
126-Jul-2019=year(B1)?
124-Aug-2016=year(B2)?
121-Jul-2000=year(B3)?

The result will be updated as shown below:

ABCD
Sr. No.DateFunctionOutput
126-Jul-2019=year(B1)2019
124-Aug-2016=year(B2)2016
121-Jul-2000=year(B3)2000

## The YEARFRAC Function

The `YEARFRAC` function returns "the year fraction that represents the number of whole days between two dates," according to Excel's documentation.

The `YEARFRAC` function has the following syntax:

``1=YEARFRAC(old_date, new_date, [type])``

The first and second arguments are required, whereas the third argument is optional. To implement the `YEARFRAC` function in Excel, consider the example given below:

ABCDE
Sr. No.Start_DateEnd_DateFunctionOutput
126-Jul-201915-08-2019=yearfrac(B1,C1)?
126-Jul-201916-08-2019=yearfrac(B2,C2)?
126-Jul-201917-08-2019=yearfrac(B3,C3)?

The result will be updated as shown below:

ABCDE
Sr. No.Start_DateEnd_DateFunctionOutput
126-Jul-201915-08-2019=yearfrac(B1,C1)0.052777778
226-Jul-201916-08-2019=yearfrac(B2,C2)0.055555556
326-Jul-201917-08-2019=yearfrac(B3,C3)0.058333333

## Conclusion

With the end of this guide, you have learned all the date and time functions with simple examples in Excel 2019.