CODEWITHSUNDEEP
dategoogle-sheetsformula
WhatData
WhatData

Reputation: 49

Function that shows numerical day of month in increments of 7 days (DAY/DATE?)

In each column header I'm looking to display only the numerical day of the first Monday of every week in the year (e.g. January 4, 2021 displays as "4" in a column header) and every subsequent column adds 7 days to that date to display the day of the next Monday.

Adding +7 to the DAY function doesn't work because it adds 7 to the number value and not the date. Any ideas?

For example, the string of column headers would look like 4, 11, 18, 25, 1, 8, 15, etc. (Jan 4, Jan 11, etc.).

Upvotes: 1

Views: 42

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(TRANSPOSE(QUERY(TEXT(ROW(INDIRECT(
 VALUE("04/01/2021")&":"&VALUE("15/02/2021"))), {"d", "ddd"}), 
 "select Col1 where Col2 = 'Mon'")))

enter image description here

update:

=ARRAYFORMULA(ARRAY_CONSTRAIN(TRANSPOSE(QUERY(TEXT(ROW(INDIRECT(
 VALUE("01/04/2021")&":"&VALUE("02/15/2050"))), {"d", "ddd"}), 
 "select Col1 where Col2 = 'Mon'")), 1, COLUMNS(E:BD)))

enter image description here

Upvotes: 1

Related Questions