Counting Parent Row for Weeks

ginamt3

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.

L_123

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

Paul Newcome

You could use something like...

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

ginamt3

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

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

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

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

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

Paul Newcome

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

ginamt3

Paul Newcome

Try this one...

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

ginamt3

Still no luck.

Paul Newcome

Ok. What do we get with

=WEEKNUMBER(Week@row)

ginamt3

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

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

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

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.