How to add week number based on the sheet summary date field?

Options

I am trying to add week number based on the sheet summary date.

For example, in the sheet summary date, I have added date i.e., 06/12/23

Then in the Week Number column, it should populate as week 1 till 06/18/23.

I have used the formula: =WEEKNUMBER(date#) - WEEKNUMBER(date#) + 1


Can anyone suggest how to do this?

Answers

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭
    Options

    Hi @sweta22

    The WEEKNUMBER function returns the week number of the 52 weeks in a year. The week number for 6/12 is 24.

    The formula you used above will always return the number 1. (X Value minus X Value plus 1 = 1).

    So to acheive what you are describing, you'll need to build table with dates and week numbers in a new sheet. Then do a cross sheet index match formula in the week number column to pull in the week number based on the date. The WEEKNUMBER function won't come into play in this option.

    I hope that helps.

    Matt

    Matt Johnson

    Sevan Technology

    Smartsheet Aligned Partner

  • sweta22
    sweta22 ✭✭✭✭
    Options

    Hi @Matt Johnson ,

    I didn't understand this. Actually, I want the week to begin on the date I've entered i.e., "Week 1" would be from June 12 to June 18, "Week 2" would be from June 19 to June 25, and so on.

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭
    Options

    Hi @sweta22

    You could make a table in another sheet. Column1 should be dates from June 12th 2023 to however farout makes sense for this situation, maybe December 31st 2025. In Column2 list Week 1 for the first 7 days, Week 2 for the next, etc. That part is tedious but shouldn't take too long at all really. Then on the original sheet do an index match formula that looks at the date on your original sheet and compares it to the new date/week table you've created, and returns the Week number.

    I hope that all makes sense. Thanks,

    Matt

    Matt Johnson

    Sevan Technology

    Smartsheet Aligned Partner

  • sweta22
    sweta22 ✭✭✭✭
    Options

    @Matt Johnson Thanks a lot for this. This may work.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!