Introduction

2

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

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

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

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," 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:

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

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.)," 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:

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," 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:

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," 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:

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," 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:

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," 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:

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," 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:

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," 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:

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

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.

2