Formula to calculate week number or week commencing date

gb82

I have a column of dates in a smartsheet, and wish to insert a formula into another column what will tell me either the weeknumber (e.g. Excel WEEKNUM formula) or the weekcommencing date.  Does anybody have any suggestions how to acheive this?

Travis

Hello gb82! This formula will calculate the number of days between a selected date cell and January 1st, 2015, then will divide by 7. This will tell you the week number of the selected date:

 

=(Date1 - DATE(2015, 1, 1)) / 7

 

This can result in a decimal. If you want it to round up to the next whole number (which would be the week you are in), use this:

 

=ROUND(((Date1 - DATE(2015, 1, 1)) / 7) + 0.49, 0)

 

 

In both these examples. Date1 is the cell containing the date you are referencing. 

gb82

thanks - that's interesting!

Rod Everett

Noticed that the WEEKNUMBER() formula has been added.

 

This calculates the week number based on the ISO 8601 International Standard.

- Weeks are measured Mon-Sun

- Weeks are measured W01 to W52/W53

- W01 is the week containing the 4th of January