# Counting Parent Row for Weeks

Options
✭✭✭✭

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.

«1

• ✭✭✭✭✭✭
Options

You could use something like...

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

• ✭✭✭✭
edited 07/01/20
Options

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?

• ✭✭✭✭✭✭
Options

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")

• ✭✭✭✭
Options

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"

• ✭✭✭✭✭✭
Options

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"

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options
• ✭✭✭✭✭✭
edited 11/12/20
Options

Try this one...

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

• ✭✭✭✭
Options

Still no luck.

• ✭✭✭✭✭✭
Options

Ok. What do we get with

=WEEKNUMBER(Week@row)

• ✭✭✭✭
Options
• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
edited 11/16/20
Options

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

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

• ✭✭✭✭✭✭
Options

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