CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
AVMathi
AVMathi

Reputation: 71

Google spreadsheet function to generate a week number for a date column?

Function to generate a week number for a date column in Google spreadsheet?

Example:

date - week
2021-01-01 0:00:00 - 
2021-01-04 0:00:00 - 2
2021-01-05 0:00:00 - 2
2021-01-10 0:00:00 - 3

Upvotes: 1

Views: 2103

Answers (2)

Aresvik
Aresvik

Reputation: 4630

If your dates are in col A, put this in cell B1:

=arrayformula({"week";if(A2:A<>"",weeknum(A2:A,2),)})

ARRAYFORMULA from cell B1 works down the sheet.

"week" puts the title (week) in cell B1, then the ; returns the line.

If A is not empty then calculate the week number weeknum(A2:A,2). If A is empty, then nothing.

The ,2 in weeknum has the week starting on Monday, ending Sunday. It looks like your example is working on Sunday to Saturday, which will need ,1 (or omitted) instead.

If you want Col B to be like your example above, you can Add A to the calc and format it using text:

=arrayformula({"date - week";if(A2:A<>"",text(A2:A,"yyyy-mm-dd h:mm:ss")&" - "&weeknum(A2:A,1),)})

Upvotes: 1

patrickb
patrickb

Reputation: 442

You should be able to use the WEEKNUM function for this!

Upvotes: 1

Related Questions