Introduction

0

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

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

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:

A | B | C | D | E |
---|---|---|---|---|

Sr. No. | Start_Date | End_Date | Holiday | WorkDays |

1 | 23-Jul-19 | 30-Jul-19 | =NETWORKDAYS(B1,C1) | |

2 | 01-Jan-19 | 31-Jan-2019 | 26-Jan-2019 | =NETWORKDAYS(B2,C2,D2) |

3 | 24-Jul-19 | 23-Aug-2019 | 15-Aug-2019 | =NETWORKDAYS(B3,C3,D3) |

The result will be updated as shown below:

A | B | C | D | E |
---|---|---|---|---|

Sr. No. | Start_Date | End_Date | Holiday | WorkDays |

1 | 23-Jul-19 | 30-Jul-19 | 6 | |

2 | 01-Jan-19 | 31-Jan-2019 | 26-Jan-2019 | 23 |

3 | 24-Jul-19 | 23-Aug-2019 | 15-Aug-2019 | 22 |

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_value | weekend_days |
---|---|

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 only |

15 | Thursday only |

16 | Friday only |

17 | Saturday only |

Let us consider a scenario where you can learn how to implement the `NETWORKDAYS.INTL`

function in Excel. Consider the example given below:

A | B | C | D | E | F |
---|---|---|---|---|---|

Sr. No. | Start_Date | End_Date | Weekend | Holiday | WorkDays |

1 | 23-Jul-19 | 30-Jul-19 | 1 | =NETWORKDAYS.INTL(B1,C1,D1) | |

2 | 01-Jan-19 | 31-Jan-2019 | 2 | 26-Jan-2019 | =NETWORKDAYS.INTL(B2,C2,D2,E2) |

3 | 24-Jul-19 | 23-Aug-2019 | 3 | 15-Aug-2019 | =NETWORKDAYS.INTL(B3,C3,D3,E3) |

The result will be updated as shown below:

A | B | C | D | E | F |
---|---|---|---|---|---|

Sr. No. | Start_Date | End_Date | Weekend | Holiday | WorkDays |

1 | 23-Jul-19 | 30-Jul-19 | 1 | 6 | |

2 | 01-Jan-19 | 31-Jan-2019 | 2 | 26-Jan-2019 | 22 |

3 | 24-Jul-19 | 23-Aug-2019 | 3 | 15-Aug-2019 | 22 |

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:

FUNCTION | OUTPUT | DETAILS |
---|---|---|

=NOW() | 25-07-2019 23:54 | return current date and time |

=NOW() + 2 | 27-07-2019 23:54 | return date and time two days in future |

=NOW() - 0.5 | 27-07-2019 11:54 | Returns the date and time 12 hours ago (-0.5 days ago) |

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:

A | B | C |
---|---|---|

Sr. No. | Time | Function |

1 | 15:45:20 PM | =SECOND(B1) |

2 | 16:28 PM | =SECOND(B2) |

3 | 12:20:30 PM | =SECOND(B3) |

The result will be updated as shown below:

A | B | C |
---|---|---|

Sr. No. | Time | Function |

1 | 15:45:20 PM | 20 |

2 | 16:28 PM | 0 |

3 | 12:20:30 PM | 30 |

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:

A | B | C | D | E |
---|---|---|---|---|

Sr. No. | Hour | Minute | Second | Output |

1 | 12 | 30 | 45 | =TIME(B1,C1,D1) |

2 | 12 | 0 | 0 | =TIME(B2,C2,D2) |

3 | 14 | 24 | 34 | =TIME(B3,C3,D3) |

The result will be updated as shown below:

A | B | C | D | E |
---|---|---|---|---|

Sr. No. | Hour | Minute | Second | Output |

1 | 12 | 30 | 45 | 0.521354167 |

2 | 12 | 0 | 0 | 0.5 |

3 | 14 | 24 | 34 | 0.600393519 |

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:

A | B | C |
---|---|---|

Sr. No. | Function | Output |

1 | =TIMEVALUE("01:30 AM") | ? |

2 | =TIMEVALUE("04:30 AM") | ? |

3 | =TIMEVALUE("04:30 PM") | ? |

The result will be updated as shown below:

A | B | C |
---|---|---|

Sr. No. | Function | Output |

1 | =TIMEVALUE("01:30 AM") | 0.0625 |

2 | =TIMEVALUE("04:30 AM") | 0.1875 |

3 | =TIMEVALUE("04:30 PM") | 0.6875 |

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:

A | B | C |
---|---|---|

Sr. No. | Function | Output |

1 | =TODAY() | ? |

2 | =TOADY() + 30 | ? |

3 | =TODAY() -30 | ? |

The result will be updated as shown below:

A | B | C |
---|---|---|

Sr. No. | Function | Output |

1 | =TODAY() | 26-07-2019 |

2 | =TOADY() + 30 | 25-08-2019 |

3 | =TODAY() - 30 | 26-06-2019 |

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_type | Number returned |
---|---|

1 or omitted | Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel. |

2 | Numbers 1 (Monday) through 7 (Sunday). |

3 | Numbers 0 (Monday) through 6 (Sunday). |

11 | Numbers 1 (Monday) through 7 (Sunday). |

12 | Numbers 1 (Tuesday) through 7 (Monday). |

13 | Numbers 1 (Wednesday) through 7 (Tuesday). |

14 | Numbers 1 (Thursday) through 7 (Wednesday). |

15 | Numbers 1 (Friday) through 7 (Thursday). |

16 | Numbers 1 (Saturday) through 7 (Friday). |

17 | Numbers 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:

A | B | C | D |
---|---|---|---|

Sr. No. | Date | Function | Output |

1 | 26-Jul-19 | =weekday(B1) | ? |

2 | 26-Jul-19 | =weekday(B2,2) | ? |

3 | 26-Jul-19 | =weekday(B3,3) | ? |

The result will be updated as shown below:

A | B | C | D |
---|---|---|---|

Sr. No. | Date | Function | Output |

1 | 26-Jul-19 | =weekday(B1) | 6 |

2 | 26-Jul-19 | =weekday(B2,2) | 5 |

3 | 26-Jul-19 | =weekday(B3,3) | 4 |

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_type | Week begins on | System |
---|---|---|

1 or omitted | Sunday | 1 |

2 | Monday | 1 |

11 | Monday | 1 |

12 | Tuesday | 1 |

13 | Wednesday | 1 |

14 | Thursday | 1 |

15 | Friday | 1 |

16 | Saturday | 1 |

17 | Sunday | 1 |

21 | Monday | 2 |

Let us consider a scenario where you can learn how to implement the `WEEKNUM`

function in Excel. Consider the example given below:

A | B | C | D |
---|---|---|---|

Sr. No. | Date | Function | Output |

1 | 26-Jul-19 | =weeknum(B1) | ? |

2 | 26-Jul-19 | =weeknum(B2,2) | ? |

3 | 26-Jul-19 | =weeknum(B3,13) | ? |

The result will be updated as shown below:

A | B | C | D |
---|---|---|---|

Sr. No. | Date | Function | Output |

1 | 26-Jul-19 | =weeknum(B1) | 30 |

2 | 26-Jul-19 | =weeknum(B2,2) | 30 |

3 | 26-Jul-19 | =weeknum(B3,13) | 31 |

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:

A | B | C | D | E |
---|---|---|---|---|

Sr. No. | Date | Working_Days | Holiday | OUTPUT |

1 | 26-07-2019 | 30 | 15-08-2019 | =WORKDAY(B1,C1,D1) |

2 | 26-07-2019 | 60 | 16-08-2019 | =WORKDAY(B2,C2,D2) |

3 | 26-07-2019 | 90 | 17-08-2019 | =WORKDAY(B3,C3,D3) |

The result will be updated as shown below:

A | B | C | D | E |
---|---|---|---|---|

Sr. No. | Date | Working_Days | Holiday | OUTPUT |

1 | 26-07-2019 | 30 | 15-08-2019 | 09-09-2019 |

2 | 26-07-2019 | 60 | 16-08-2019 | 21-10-2019 |

3 | 26-07-2019 | 90 | 17-08-2019 | 29-11-2019 |

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-number | Weekend days |
---|---|

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 only |

15 | Thursday only |

16 | Friday only |

17 | Saturday only |

Let us consider a scenario where you can learn how to implement the `WORKDAY.INTL`

function in Excel. Consider the example given below:

A | B | C | D | E | F |
---|---|---|---|---|---|

SR. NO. | DATE | WORKING_DAYS | HOLIDAY | WEEKEND_NUMBER_VALUES | OUTPUT |

1 | 26-07-2019 | 30 | 15-08-2019 | 2 | =WORKDAY.INTL(B1,C1,E1,D1) |

2 | 26-07-2019 | 60 | 16-08-2019 | 3 | =WORKDAY.INTL(B2,C2,E2,D2) |

3 | 26-07-2019 | 90 | 17-08-2019 | 7 | =WORKDAY.INTL(B3,C3,E3,D3) |

The result will be updated as shown below:

A | B | C | D | E | F |
---|---|---|---|---|---|

SR. NO. | DATE | WORKING_DAYS | HOLIDAY | WEEKEND_NUMBER_VALUES | OUTPUT |

1 | 26-07-2019 | 30 | 15-08-2019 | 2 | 07-09-2019 |

2 | 26-07-2019 | 60 | 16-08-2019 | 3 | 19-10-2019 |

3 | 26-07-2019 | 90 | 17-08-2019 | 7 | 28-11-2019 |

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:

A | B | C | D |
---|---|---|---|

Sr. No. | Date | Function | Output |

1 | 26-Jul-2019 | =year(B1) | ? |

1 | 24-Aug-2016 | =year(B2) | ? |

1 | 21-Jul-2000 | =year(B3) | ? |

The result will be updated as shown below:

A | B | C | D |
---|---|---|---|

Sr. No. | Date | Function | Output |

1 | 26-Jul-2019 | =year(B1) | 2019 |

1 | 24-Aug-2016 | =year(B2) | 2016 |

1 | 21-Jul-2000 | =year(B3) | 2000 |

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:

A | B | C | D | E |
---|---|---|---|---|

Sr. No. | Start_Date | End_Date | Function | Output |

1 | 26-Jul-2019 | 15-08-2019 | =yearfrac(B1,C1) | ? |

1 | 26-Jul-2019 | 16-08-2019 | =yearfrac(B2,C2) | ? |

1 | 26-Jul-2019 | 17-08-2019 | =yearfrac(B3,C3) | ? |

The result will be updated as shown below:

A | B | C | D | E |
---|---|---|---|---|

Sr. No. | Start_Date | End_Date | Function | Output |

1 | 26-Jul-2019 | 15-08-2019 | =yearfrac(B1,C1) | 0.052777778 |

2 | 26-Jul-2019 | 16-08-2019 | =yearfrac(B2,C2) | 0.055555556 |

3 | 26-Jul-2019 | 17-08-2019 | =yearfrac(B3,C3) | 0.058333333 |

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

0