Author avatar

Chhaya Wagmi

Working with Time and Date Functions in Excel - Part 2

Chhaya Wagmi

  • Aug 6, 2019
  • 17 Min read
  • 13 Views
  • Aug 6, 2019
  • 17 Min read
  • 13 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. There are various time and date functions which are present in the Excel 2019 version, as mentioned below:

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

Time and Date Functions

In this section, you will learn about each of these time and date functions through various scenarios.

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.

The NETWORKDAYS function has the following syntax:

1
=NETWORKDAYS(start_date, end_date, [holidays])

The arguments start_date and end_date are required arguments in the function. These two arguments represent the start date and the end date. The third argument in the function is optional. It can be a range of cells containing dates in the DATE format or an array constant of the serial numbers as the dates in DATE format.

Let us consider a scenario where you can learn how 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 avails you the option to select the day(s) that you want to select as the weekend which makes it different than the NETWORKDAYS function.

The NETWORKDAYS.INTL function has the following syntax:

1
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

The first argument, start_date, and the second argument, end_date, are required arguments in the function whereas the third argument, weekend, and the fourth argument, holidays, are optional. The value of the third argument, weekend, represents the following days:

weekened_valueweekend_days
1 or omittedSaturday, Sunday
2Sunday, Monday
3Monday, Tuesday
4Tuesday, Wednesday
5Wednesday, Thursday
6Thursday, Friday
7Friday, Saturday
11Sunday only
12Monday only
13Tuesday only
14Wednesday only
15Thursday only
16Friday only
17Saturday only

Let us consider a scenario where you can learn how 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 us 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.

The NOW function has the following syntax:

1
=NOW()

The NOW function has no arguments. Let us consider a scenario where you can learn how to implement the NOW function in Excel. Consider the example given below:

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.

The SECOND function has the following syntax:

1
=SECOND(time_value/ serial_number)

Let us consider a scenario where you can learn how 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 return us 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.).

The TIME function has the following syntax:

1
=TIME(hour, minute, second)

Let us consider a scenario where you can learn how 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.).

The TIMEVALUE function has the following syntax:

1
=TIMEVALUE(time_text)

Let us consider a scenario where you can learn how 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.

The TODAY function has the following syntax:

1
=TODAY()

The TODAY function has no argument. Let us consider a scenario where you can learn how 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.

The WEEKDAY function has the following syntax:

1
=WEEKDAY(serial_number/date,[return_type])

The first argument in the function is required, whereas the second argument is optional and is a number that determines the type of return value. Consider the table given below for second argument:

Return_typeNumber returned
1 or omittedNumbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
2Numbers 1 (Monday) through 7 (Sunday).
3Numbers 0 (Monday) through 6 (Sunday).
11Numbers 1 (Monday) through 7 (Sunday).
12Numbers 1 (Tuesday) through 7 (Monday).
13Numbers 1 (Wednesday) through 7 (Tuesday).
14Numbers 1 (Thursday) through 7 (Wednesday).
15Numbers 1 (Friday) through 7 (Thursday).
16Numbers 1 (Saturday) through 7 (Friday).
17Numbers 1 (Sunday) through 7 (Saturday).

Let us consider a scenario where you can learn how 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. The input provided to the function must be in date format or else it will cause an error.

The WEEKNUM function has the following syntax:

1
=WEEKNUM(serial_number,[return_type])

The first argument in the function is required whereas the second argument is optional and is a number that determines on which day the week begins. The default value is 1. Consider the table given below for the second argument.

Return_typeWeek begins onSystem
1 or omittedSunday1
2Monday1
11Monday1
12Tuesday1
13Wednesday1
14Thursday1
15Friday1
16Saturday1
17Sunday1
21Monday2

Let us consider a scenario where you can learn how 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.

The WORKKDAY function has the following syntax:

1
=WORKDAY(start_date, days, [holidays])

The first and the second argument are required, whereas the third argument is optional. You can use the third argument when you have a list of holidays that you want to include for calculation.

Let us consider a scenario where you can learn how to implement the WORKKDAY 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.

The WORKDAY.INTL function has the following syntax:

1
=WORKDAY.INTL(start_date, days, [weekend], [holidays])

The first and second arguments are optional. The third argument and the fourth argument are optional. The third argument is a weekend number or string that specifies when weekends occur. The weekend number values indicates the following weekend days.

weekend-numberWeekend days
1 or omittedSaturday, Sunday
2Sunday, Monday
3Monday, Tuesday
4Tuesday, Wednesday
5Wednesday, Thursday
6Thursday, Friday
7Friday, Saturday
11Sunday only
12Monday only
13Tuesday only
14Wednesday only
15Thursday only
16Friday only
17Saturday only

Let us consider a scenario where you can learn how 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.

The YEAR function has the following syntax:

1
=YEAR(serial_number)

Let us consider a scenario where you can learn how 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 start_date and end_date.

The YEARFRAC function has the following syntax:

1
=YEARFRAC(start_date, end_date, [basis])

The first and second arguments are required, whereas the third argument is optional. Let us consider a scenario where you can learn how 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.

0