## Formula to calculate week number or week commencing date

#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Formula to calculate week number or week commencing date

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?

• 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.

• thanks - that's interesting!

• 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

This discussion has been closed.