Help with Weeknumber function that takes into consideration years.

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


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

  • 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.



    Projects Starting "2" weeks out



    Projects Starting "3" weeks out



  • Paul Newcome
    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!