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

edited 12/09/19 in Archived 2015 Posts

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?


  • TravisTravis Employee

    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.