weeks Archives - Sheets Help

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.

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.