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

gb82 ✭✭✭✭
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?


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

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

This discussion has been closed.