Author avatar

Chhaya Wagmi

Working with Time and Date Functions in Excel - Part 2

Chhaya Wagmi

  • Sep 14, 2020
  • 16 Min read
  • 560 Views
  • Sep 14, 2020
  • 16 Min read
  • 560 Views
Business Professional
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()?
2=TOADY() + 30?
3=TODAY() -30?

The result will be updated as shown below:

ABC
Sr. No.FunctionOutput
1=TODAY()26-07-2019
2=TOADY() + 3025-08-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.

2