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

Comments

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

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