NETWORKDAYS Function – Google Sheets

The NETWORKDAYS function calculates the number of working days between two dates. The function excludes weekends (Saturday and Sunday) and can exclude holidays if you provide a list.

Syntax

=NETWORKDAYS(start_date,end_date,[holiday(s)])

start_date Date at which to start the calculation

end_date Date at which to end the calculation

[holiday(s)] An optional list of holidays to exclude from the count of days.

Related functions

DAYS – Calculate the number of days between two dates.

DATEDIF – Calculate length of time between two dates in years, months, and/or days.

MINUS – Subtract one value from another. Can be done with dates.

NETWORKDAYS.INTL – Calculate the number of work days between two dates. You can modify which days are weekends.

Errors

#VALUE! – The inputs are something that isn’t a valid date such as “The other day” or “Yester-yester-day”.

Examples

 Live examples in Sheets

Go to this spreadsheet for the examples of the NETWORKDAYS function shown above that you can study and use anywhere you would like.

NETWORKDAYS.INTL Function – Google Sheets

The NETWORKDAYS.INTL calculates the number of working days between two dates. The function excludes weekends (which may or may not be Saturday and Sunday) and can exclude holidays if you provide a list.

Syntax

=NETWORKDAYS.INTL(start_date,end_date,[weekend day(s)],[holiday(s)])

start_date Date at which to start the calculation

end_date Date at which to end the calculation

[weekend day(s)] An optional description of which days are weekends.

  • Number method
    • 1 – Saturday and Sunday [don’t need to specify]
    • 2 – Sunday and Monday
    • 3 – Monday and Tuesday
    • 4 – Tuesday and Wednesday
    • 5 – Wednesday and Thursday
    • 6 – Thursday and Friday
    • 7 – Friday and Saturday
    • 11 – Sunday only
    • 12 – Monday only
    • 13 – Tuesday only
    • 14 – Wednesday only
    • 15 – Thursday only
    • 16 – Friday only
    • 17 – Saturday only
  • String method – Each digit represents a day. First digit is Monday.
    • 0000011 – Saturday and Sunday
    • 0100000 – Only Tuesday

[holiday(s)] An optional list of holidays to exclude from the count of days.

Related functions

DAYS – Calculate the number of days between two dates.

DATEDIF – Calculate length of time between two dates in years, months, and/or days.

MINUS – Subtract one value from another. Can be done with dates.

NETWORKDAYS – Calculate the number of working days without the weekend modifications of NETWORKDAYS.INTL

Errors

#VALUE! – The inputs are something that isn’t a valid date such as “The other day” or “Yester-yester-day”.

Examples

 Live examples in Sheets

Go to this spreadsheet for the examples of the NETWORKDAYS.INTL function shown above that you can study and use anywhere you would like.

TIMEVALUE Function – Google Sheets

The TIMEVALUE function converts a time to a fraction of 1. Google Sheets works with times as fractions of a number, not as text strings. This function converts times into their usable form. Noon would be .5 and 6 PM would be .75.

The function can be used with 12 hour AM/PM time or with 24 hour time. If a date and time is given to the function, the date will be ignored, but the time will be converted. To represent seconds, use a second colon. 5 minutes and 12 seconds would be “00:05:12” not “05:12”.

Remember that dates are really just saved as numbers by your spreadsheet with date formatting applied. The date must be in a valid format, so you have to use the DATE function, wrap it in quotes, or use a cell reference to a cell containing a date.

Syntax

=TIMEVALUE(time_text)

time_text Text representation of a time.

Related functions

DATEVALUE – Converts a date to a numeric value in a similar way as TIMEVALUE.

HOUR – Returns the hour value from a time

MINUTE – Returns the minute value from a time

SECOND – Returns the second value from a time

Errors

#VALUE! – The inputs are something that isn’t a valid time such as “A minute ago” or “Then”.

#NUM – An input is a number but is not a valid time.

Examples

Embedded example

Video example

Below is an example of this function being used to create an employee timesheet.

 

 Live examples in Sheets

Go to this spreadsheet for the examples of the TIMEVALUE function shown above that you can study and use anywhere you would like.

DATEDIF Function – Google Sheets

The DATEDIF function calculates the difference between two days. The difference can be returned in day, month, year or a combination thereof. Whereas the DAYS function will only return days, this function has multiple output options.

The input for this function needs to be either surrounded by quotes or a reference to another cell with a date in it. If your days, months, and years are in different cells, use the DATE function to combine them. If you want to know the current age, use TODAY() as the end date.

Related functions

DAYS – Simple duration of time formula with just days.

MINUS – Subtract one value from another. Can be done with dates.

Errors

#NUM – The starting date is after the beginning date.

Purpose

The DATEDIF function returns the number of day, month, or years between two dates.

Syntax

=DATEDIF(start_date,end_date,unit)

start_date Date at which to start the calculation

end_date Date at which to end the calculation

unit Type of output. Choices are “Y”, “M”, “D”, “YM”, “YD”, or “MD”.

  • Y – Number of whole years elapsed between start and end dates
    • YM – Number of months elapsed after the number of years shown with the “Y” unit. Will not exceed 11.
    • YD – Number of days elapsed after the number of years shown with the “Y” unit. No matter how many days after the last year, starts counting after end of last full year and is never over 364.
  • M – Number of whole months elapsed between start and end dates
    • MD – Number of days elapsed after the number of months shown with the “M” or “YM” unit. Can’t go higher than 30.
  • D – Number of whole days elapsed between start and end dates

 

Examples

Example 1 – Plain and Simple

Formula Result
=DATEDIF(“March 1,2017″,”March 3, 2017″,”D”) 2

This function returns two days, the same as if you did ="3/3/17"-"3/1/17".

Example 2 – Different outputs

DATEDIF example

Examples showing DATEDIF being used with different parameters.

 Live examples in Sheets

Go to this spreadsheet for the examples of the DATEDIF function shown above that you can study and use anywhere you would like.

WORKDAY.INTL Function – Google Sheets

The WORKDAY.INTL function calculates a workday a given number of days away from a date. You can optionally specify a list of holidays to exclude from the count and different weekends. If your days, months, and years are in different cells, use the DATE function to combine them. This function is different from the WORKDAY function because you can also specify different weekends.

Syntax

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

  • start_date – The date from which to start counting the days.
  • days – The number of days to add to the start_date
  • [weekend]
    • Number method
      • 1 – Saturday and Sunday [don’t need to specify]
      • 2 – Sunday and Monday
      • 3 – Monday and Tuesday
      • 4 – Tuesday and Wednesday
      • 5 – Wednesday and Thursday
      • 6 – Thursday and Friday
      • 7 – Friday and Saturday
      • 11 – Sunday only
      • 12 – Monday only
      • 13 – Tuesday only
      • 14 – Wednesday only
      • 15 – Thursday only
      • 16 – Friday only
      • 17 – Saturday only
    • String method – Each digit represents a day. First digit is Monday.
      • 0000011 – Saturday and Sunday
      • 0100000 – Only Tuesday
  • [holidays] – Optional specification of which days are to be excluded from the count of work days.

Related functions

WEEKDAY – Returns the week number for a given date.

DAYS – Find the number of days between two dates.

NETWORKDAYS.INTL – Calculate the number of working days in between two days and exclude specified days as weekends.

NETWORKDAYS – Calculate the number of working days in between two days and exclude weekends.

WORKDAY – Like this function but with Saturday and Sunday as the weekends only.

Errors

#NUM – The inputs are numbers but are not valid dates. This could happen if you used the 35th day of November, “11/35/2018”.

#VALUE! – The inputs are something that doesn’t convert to a number such as “The other day” or “Yester-yester-day”.

 Live examples in Sheets

Go to this spreadsheet for the examples of the WORKDAY.INTL function shown above that you can study and use anywhere you would like.

WORKDAY Function – Google Sheets

The WORKDAY function calculates a workday a given number of days away from a date. You can optionally specify a list of holidays to exclude from the count. If your days, months, and years are in different cells, use the DATE function to combine them.

Syntax

=WORKDAY(start_date,days,[holidays])

  • start_date – The date from which to start counting the days.
  • days – The number of days to add to the start_date
  • [holidays] – Optional specification of which days are to be excluded from the count of work days.

Related functions

WEEKDAY – Returns the week number for a given date.

DAYS – Find the number of days between two dates.

NETWORKDAYS.INTL – Calculate the number of working days in between two days and exclude specified days as weekends.

NETWORKDAYS – Calculate the number of working days in between two days and exclude weekends.

WORKDAY.INTL – Like this function but with movable weekends.

Errors

#NUM – The inputs are numbers but are not valid dates. This could happen if you used the 35th day of November, “11/35/2018”.

#VALUE! – The inputs are something that doesn’t convert to a number such as “The other day” or “Yester-yester-day”.

 Live examples in Sheets

Go to this spreadsheet for the examples of the WORKDAY function that you can study and use anywhere you would like.

DAYS Function – Google Sheets

The DAYS function is a simple date function that lets you calculate the number of days between two dates. The same result can arrived at by simply subtracting two dates such as: ="7/31/2016"-"6/5/1991".

Purpose

The DAYS function returns the number of days between two dates.

Related functions

DATEDIF – Calculate length of time between two dates in years, months, and/or days.

MINUS – Subtract one value from another. Can be done with dates.

Errors

#NUM – The inputs are numbers but are not valid dates. This could happen if you used the 35th day of November, “11/352018”.

#VALUE! – The inputs are something that doesn’t convert to a number such as “The other day” or “Yester-yester-day”.

Syntax

=DAYS(end_date,start_date)

Note that, for some reason, the function wants the ending date first, then the starting date.

Video Explanation

Examples

A few simple examples of the DAYS function.

Formula Description Result
=DAYS("7/31/2017","7/30/2016") Number of days between 7/30/2016 and 7/31/2017 366
=DAYS("1/7/2020","12/5/2015") Number of days between 12/5/2015 and 1/7/2020 1,494
=DAYS(C3,"2/9/1994") Number of days between 3/5/2001 (assuming that the value was in C3) and 2/9/1994 -2,581

 Live examples in Sheets

Go to this spreadsheet for examples of the DAYS function that you can study and use anywhere you would like.