CODEWITHSUNDEEP
dategoogle-sheetsgoogle-sheets-formulaarray-formulasweek-number
iku
iku

Reputation: 23

How to get date the same as week number and day of week of previous year using Google Sheets?

I'm looking for a way to get the date the same as week number and day of the week of the previous year in Google Sheets.

For instance,

Input   : 12/22/2018 Saturday of week 51.
Output  : 12/23/2017 Saturday of week 51.

But there is an exception, like a this,

Input   : 12/31/2018 Monday of week 53.
Output  : 01/01/2018 Saturday of week 1.

Input   : 01/01/2017 Monday of week 1.
Output  : 01/03/2016 Sunday of week 2.

Also want to get ISOweek.

For instance,

Input   : 01/02/2016 Saturday of week 53.
Output  : 01/03/2015 Saturday of week 1.

Upvotes: 2

Views: 909

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(TEXT(IFERROR(VLOOKUP(TEXT(A1, "ddd")&WEEKNUM(A1, 1), {TEXT(ROW(INDIRECT(
  "A"&DATEVALUE(DATE(YEAR(A1)-1, MONTH(A1), DAY(A1)))-14&
 ":A"&DATEVALUE(DATE(YEAR(A1)-1, MONTH(A1), DAY(A1)))+14)), "ddd")&WEEKNUM(ROW(INDIRECT(
  "A"&DATEVALUE(DATE(YEAR(A1)-1, MONTH(A1), DAY(A1)))-14&
 ":A"&DATEVALUE(DATE(YEAR(A1)-1, MONTH(A1), DAY(A1)))+14)), 1), ROW(INDIRECT(
  "A"&DATEVALUE(DATE(YEAR(A1)-1, MONTH(A1), DAY(A1)))-14&
 ":A"&DATEVALUE(DATE(YEAR(A1)-1, MONTH(A1), DAY(A1)))+14))}, 2, 0), 
 VLOOKUP(IF(WEEKNUM(A1, 1)+1=54, 1, WEEKNUM(A1, 1)+1), {WEEKNUM(ROW(INDIRECT(
  "A"&DATEVALUE(DATE(YEAR(A1)-1, MONTH(A1), DAY(A1)))-14&
 ":A"&DATEVALUE(DATE(YEAR(A1)-1, MONTH(A1), DAY(A1)))+14)), 1), ROW(INDIRECT(
  "A"&DATEVALUE(DATE(YEAR(A1)-1, MONTH(A1), DAY(A1)))-14&
 ":A"&DATEVALUE(DATE(YEAR(A1)-1, MONTH(A1), DAY(A1)))+14))}, 2, 0)), "yyyy/mm/dd"))

0

Upvotes: 0

Related Questions