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

@Paul Newcome I do that all the time. Usually it's because I get sidetracked on the main functionality of the formula rather than the problem.
My suggested formula is a little fancy, in that the formula checks from the current sunday to have the same output as the previous solutions suggested, but if you want to check from the current date to next date you could potentially simplify this further.
=ROUNDUP((Week@row  TODAY()) / 7) + " Weeks Out"
Answers

You could use something like...
=WEEKNUMBER(Week@row)  WEEKNUMBER(TODAY()) + " weeks out"

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?

The WEEKNUMBER function actually operates on a MondaySunday 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")

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"

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"

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

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


Try this one...
=(WEEKNUMBER(Week@row) + 1 + IF(YEAR(Week@row) > YEAR(TODAY()), 52))  WEEKNUMBER(TODAY()) + "weeks out"

Still no luck.



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?

That's a little complicated.... try this
=ROUNDUP((Week@row  (TODAY()  WEEKDAY(TODAY()) + 1)) / 7) + " Weeks Out"
Help Article Resources
Categories
Check out the Formula Handbook template!