Help with Weeknumber function that takes into consideration years.

ACS

I would like to do a if Statement

I have a column called "Date" w/ a Date

I have a column called "Health" which is a color (red,yellow)

I would like to do a formula in my Health column

If "Date" is Next Week then "Health" = Yellow

If "Date" is Last Week then "Health" = Red

Requirements

-I would like to not count by days.. as that is different result.

-I would like it to work across different years

for example

if today is Dec 1 2022 and Date = Jan 1 2023

if today is Jan 1 2023 and Date = Dec 1 2022

In summary...

It should function like WeekNumber function but take into account Years.... basically.

Paul Newcome

Are you trying to output a week number or are you trying to get a count of one of the colors where the date is (for example) last week? I'm also not sure I follow your example as the dates are more than 1 week apart yet your column names reference only 1 week difference (last or next).

ACS

Hi Paul.. thanks for assisting..

I am trying to get a number for # of Weeks Out a Project Starts.. (not days or work days)

I do not want to use # of days or work days, as I prefer to look on Monday, to see everything with a start date next week..(M-F)

Or if I run the report on a Friday, I see all the projects to start next week (Monday to Friday).. same report as above

Being as we are approaching a new year.... the calculation needs to take into account years..

(which is where I'm stuck)

eg: Week 1 in year 2023 minus Week 52 of 2022 is actually "1" week.

My end result is in my reports.. I'm grouping my rows by

Projects Starting "1" weeks out.

A

B

Projects Starting "2" weeks out

C

D

Projects Starting "3" weeks out

E

F

Paul Newcome

I understand that you want to work with weeks and that it needs to accommodate moving into a new year. What I didn't understand from your original post was what exactly the intended result was.

Now instead of colors it looks like you want to basically do a countdown?