Counting Parent Row for Weeks

I want to be able to Count the yellow parent weeks to say how many weeks out from current week is. For example week of 6/28 is current week. I want the proceeding weeks to say for example

7/12/20 - 3 weeks out

7/19/20 4 weeks out

The current week always gets moved to another sheet once the week is over.


Best Answer

«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You could use something like...

    =WEEKNUMBER(Week@row) - WEEKNUMBER(TODAY()) + " weeks out"

  • ginamt3
    ginamt3 ✭✭✭✭
    edited 07/01/20

    Hi Paul,

    Discard below. I got it to work:)


    Thank you for a quick response.. not sure what is going on for the first 3 week displays? Can I add a plus 1 somewhere so I don't get a 0 weeks out?



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The WEEKNUMBER function actually operates on a Monday-Sunday basis. So Sunday the 28th of June is actually considered to be a week number less than Monday the 29th. That would be the first row's issue. To correct this we would either need to adjust your dates to show Mondays, or we would need to rework the formula completely. It is your choice. Just let me know.


    The second row showing "0 weeks out" can be remedied like so:

    =IF(WEEKNUMBER(Week@row) = WEEKNUMBER(TODAY()), "insert current week text of choice here", WEEKNUMBER(Week@row) - WEEKNUMBER(TODAY()) + " weeks out")


    The third row showing "1 weeks out" is because we only specified "weeks out" for the text. This can be adjusted for by using the bold portion below.

    =IF(WEEKNUMBER(Week@row) = WEEKNUMBER(TODAY()), "insert current week text of choice here", WEEKNUMBER(Week@row) - WEEKNUMBER(TODAY()) + " week" + IF(WEEKNUMBER(Week@row) - WEEKNUMBER(TODAY()) > 1, "s") + " out")

  • ginamt3
    ginamt3 ✭✭✭✭

    Hi Paul,

    This is working fine until now we Have a January date and it calculates backwards.

    Using the formula. I know I have to apply the year?

    =WEEKNUMBER(Week@row + 1) - WEEKNUMBER(TODAY()) + " weeks out"


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Hmm... I don't have time to test, but what if you used an IF statement to compare the years and add 52 if the year is greater than the current year?


    =WEEKNUMBER(Week@row + 1 + IF(YEAR(Week@row) > YEAR(TODAY()), 52)) - WEEKNUMBER(TODAY()) + " weeks out"

  • ginamt3
    ginamt3 ✭✭✭✭

    Trying to play around with syntax, something is a miss..


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What is the exact formula in use in the most recent screenshot?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 11/12/20

    Try this one...


    =(WEEKNUMBER(Week@row) + 1 + IF(YEAR(Week@row) > YEAR(TODAY()), 52)) - WEEKNUMBER(TODAY()) + "weeks out"

  • ginamt3
    ginamt3 ✭✭✭✭
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. What do we get with

    =WEEKNUMBER(Week@row)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah. There lies the problem. Getting the correct week number from the first week of the next year can get a little wonky at times. Let's see if we can work in an IF statement to accommodate this one...


    So if the month is 1 and the year is greater than the current year and the week number is greater than or equal to 52, then really we want week 1, otherwise we want the normally generated week number.


    =IF(AND(MONTH(Week@row) = 1, YEAR(Week@row) > YEAR(TODAY()), WEEKNUMBER(Week@row) >= 52), 1, WEEKNUMBER(Week@row))


    Drop that into the current WEEKNUMBER(Week@row) portion, and we should be good to go...


    =(IF(AND(MONTH(Week@row) = 1, YEAR(Week@row) > YEAR(TODAY()), WEEKNUMBER(Week@row) >= 52), 1, WEEKNUMBER(Week@row)) + IF(YEAR(Week@row) > YEAR(TODAY()), 52)) - WEEKNUMBER(TODAY()) + "weeks out"


    How does that one work?

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 11/16/20

    That's a little complicated.... try this

    =ROUNDUP((Week@row - (TODAY() - WEEKDAY(TODAY()) + 1)) / 7) + " Weeks Out"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @L@123 Duh! I was completely overthinking it. Thanks for the input. So much more simple...


    @ginamt3 I would give L@123's suggestion a try. Instead of trying to accommodate week numbers, you would count the days, divide by 7 to get how many weeks, then use the ROUNDUP function to give a clean number.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!