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.

Find more posts tagged with

Accepted answers

All comments

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.

Projects Starting "2" weeks out

Projects Starting "3" weeks out

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.