How to get WeekNumber function in Google Spreadsheets

Sometimes you want to have the week number of a certain date in your spreadsheet. In Excel you can use the WEEKNUM() function for this purpose, however in Google Spreadsheets there is no such function built in.

If you want to get a function that does the same as WEEKNUM() in Google Spreadsheets, use the following trick (assuming cell A1 holds a valid date):

WeekNum in Google Spreadsheets

=MID(TEXT(A1,"yyww"), 3, 2)

That’s it! This will output the weeknumber of the date in cell A1.

If you want to learn more about the MID() you can check this article which explains how to get a letter in the alphabet based on it’s index number.

  • brettalton

    What about writing “Weeks 5-7”?

  • No longer works, unfortunately (returns “ww” for any date, as TEXT does not support the “ww” marker.)