Date parts Archives - Sheets Help

DATEDIF Function – Google Sheets

Posted on January 13, 2018 | by Adam Steinfurth | Leave a Comment

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

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.

ISOWEEKNUM Function – Google Sheets

Posted on January 13, 2018 | by Adam Steinfurth | Leave a Comment

The ISOWEEKNUM function accepts a date and returns the ISO week number (1 through 54) of that date. 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.

An ISO week starts on Monday. The first week of the year is the first week containing a Thursday in that year.

If your days, months, and years are in different cells, use the DATE function to combine them.

Syntax

=ISOWEEKNUM(date)

date – The date for which you want the ISO week number. This is required.

Related functions

DATE – Takes separate year, month, and day values and returns them as a date

DATEVALUE – Returns the serial value of a date

WEEKNUM – Returns the number of a given week

Errors

#NUM – The inputs are numbers but are not valid dates for the date input or the type input is out of range. This could happen if you use a negative number for the date.

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

Examples

Example 1 – Plain and Simple

Formula	Result
=ISOWEEKNUM(“March 1,2017”)	9

Example 2 – ISOWEEKNUM with DATE function

Formula	Result
=ISOWEEKNUM(DATE(2019,11,7))	45

Live examples in Sheets

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

NETWORKDAYS Function – Google Sheets

Posted on January 13, 2018 | by Adam Steinfurth | Leave a Comment

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

Posted on January 13, 2018 | by Adam Steinfurth | 2 Comments

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.

TIME Function – Google Sheets

Posted on January 13, 2018 | by Adam Steinfurth | Leave a Comment

The TIME function takes three numbers and converts them to a time. The hour is input as 0-24 and therefore therefore you don’t need to enter AM/PM.

Syntax

=TIME(hour,minute,second)

hour Number that represents the hour (0-23).

minute Number that represents the minute (0-59).

second Number that represents the second (0-59).

Related functions

DATE – In a similar way to the TIME function, DATE takes three numbers and converts them to a date.

Errors

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

Examples

Live examples in Sheets

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

TIMEVALUE Function – Google Sheets

Posted on January 13, 2018 | by Adam Steinfurth | Leave a Comment

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.

WEEKDAY Function – Google Sheets

Posted on January 13, 2018 | by Adam Steinfurth | Leave a Comment

The WEEKDAY function accepts a date and returns the day number of that date. 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.

If your days, months, and years are in different cells, use the DATE function to combine them.

Syntax

=WEEKDAY(date,[type])

date – The date for which you want the week number. This is required.
[type] – Optional specification of which numbers scheme to use for the days of the week,
- 1 – Sunday = 1, Monday = 2, etc.
- 2 – Monday = 1, Tuesday = 2, etc.
- 3 – Monday = 0, Tuesday = 1, etc.

Related functions

DATE – Takes separate year, month, and day values and returns them as a date

DATEVALUE – Returns the serial value of a date

DAY – Returns the value the day from a given date

Errors

#NUM – The input is a number but is not a valid date. This could happen if you used the 35th day of November.

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

Examples

Live examples in Sheets

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

WEEKNUM Function – Google Sheets

Posted on January 13, 2018 | by Adam Steinfurth | Leave a Comment

The WEEKNUM function accepts a date and returns the week number (1 through 54) of that date. 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.

If your days, months, and years are in different cells, use the DATE function to combine them.

Syntax

=WEEKNUM(date,[type])

date – The date for which you want the week number. This is required.
[type] – Optional specification of which day is the first day of the week. And, which week is the first week of the year.
- 1 (or not specified) – Week begins on Sunday
- 2 – Week begins on Monday
- 11 – Week begins on Monday (same as #2)
- 12 – Week begins on Tuesday
- 13 – Week begins on Wednesday
- 14 – Week begins on Thursday
- 15 – Week begins on Friday
- 16 – Week begins on Saturday
- 17 – Week begins on Sunday
- 21 – Week begins on Monday and the week containing the first Thursday of the year is numbered week 1. See ISOWEEKNUM for more.

Related functions

DATE – Takes separate year, month, and day values and returns them as a date

DATEVALUE – Returns the serial value of a date

ISOWEEKNUM – Returns the ISO day of the week. Similar to WEEKNUM.

Errors

#NUM – The inputs are numbers but are not valid dates for the date input or the type input is out of range. This could happen if you use a negative number for the date or a number for type that is not one of the preset options (i.e. 1, 2, 11).

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

Examples

Live examples in Sheets

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

WORKDAY.INTL Function – Google Sheets

Posted on June 29, 2017 | by Adam Steinfurth | Leave a Comment

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.

DATEVALUE Function – Google Sheets

Posted on June 29, 2017 | by Adam Steinfurth | 3 Comments

The DATEVALUE function takes a date value in any valid format and returns the same date as a serial number. All dates and times are stored as serial in Google Sheets so they will work in formulas (you can add March plus June).

The DATEVALUE function can be used to prepare dates to be sorted, filtered, or used in formulas.

The input for this function needs to be either surrounded by quotes or a reference to another cell with a date in it.

Purpose

The DATEVALUE function returns a serial number from any valid date.

Syntax

=DATEVALUE(date_text)

date_text – The date, written in a text format, that is to be converted

Related functions

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

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 date such as “A minute ago” or “Then”.

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

Examples

Example 1 – Plain and Simple

Formula	Result
=DATEVALUE(“March 1, 2017”)	March 1, 2017

This example has a date-value that Sheets recognizes so it is able to return a serial number. In this case, the formatting is set to date, so the serial number is shown as March 1, 2017. If the formatting were set to number, the result of the function would be 42,795.00.

Example 2 – Unrecognized text date

Formula	Result
=DATEVALUE(“March 1st, 2017”)	#VALUE!

A full date typed as 1st instead of 1 returns a #VALUE! error. The #VALUE! error occurs whenever Sheets cannot figure out the text date.

Example 3 – Just a day and month

Formula	Result
=DATEVALUE(“March 1”)	March 1, 2017

Just a day and a month typed in. The spreadsheet assumes the date should be the current year which was 2017 when this was written. The year value will always be current when viewed in the live spreadsheet.

Example 4 – Used in an employee timesheet

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 DATEVALUE function shown above that you can study and use anywhere you would like.