Help with cross year Working week formula

Dave Mex

Hello Everyone. I'm after help making this work in a single column if possible

I'm wanting to create a Due Column that will tell you if the end date of the task you are working on is in "This week", "Next Week" or "Beyond". The problem occurs when the task runs from December to January. As you can see from the above it gives an incorrect return. The Formula I'm using is.

=IFERROR(
IF(AND(WEEKNUMBER([End Date]@row) <= WEEKNUMBER([Working Day]@row), YEAR([End Date]@row) = YEAR([Working Day]@row)), "This Week",
IF(AND(WEEKNUMBER([End Date]@row) = (WEEKNUMBER([Working Day]@row) + 1), YEAR([End Date]@row) = YEAR([Working Day]@row)), "Next Week",
IF(AND(WEEKNUMBER([End Date]@row) = (WEEKNUMBER([Working Day]@row) + 2), YEAR([End Date]@row) = YEAR([Working Day]@row)), "2 Weeks Out",
IF(AND(WEEKNUMBER([End Date]@row) >= (WEEKNUMBER([Working Day]@row) + 3), YEAR([End Date]@row) >= YEAR([Working Day]@row)), "Due Beyond")))), "error")

I have worked on the below formula that gives me the week number when across this time frame but I'm struggling in incorporating it into my current formula.

=IF(YEAR([End Date]@row) > YEAR([Working Day]@row), (WEEKNUMBER([End Date]@row) + 52) - WEEKNUMBER([Working Day]@row), WEEKNUMBER([End Date]@row) - WEEKNUMBER([Working Day]@row))

This can be made to work with a support column or support sheet. However these reports are very large so I'm after saving bandwidth. And need it to work within one column formula.

Any help would me much appreciated.

SoS | Dan Palenchar

Hello @Dave Mex,

I'd recommend reproducing the solution I developed below.

In short:

• Use Sheet Summary to add some Date field with formulas that return the starting/ending day of the current and following weeks
• Reference these fields with an IF() statement to check a data column and return the desired text (e.g., "This Week", "Next Week".

The formula that will output the Monday of the current week is =IF(WEEKDAY(TODAY()) = 1, TODAY(-6), TODAY() - (WEEKDAY(TODAY()) - 2))

You can then add to this to get the end of the week (+6), start of next week (+7), and so on. These will work fine with respect to crossing December > January:

Published Sheet below (full screen link)

https://app.smartsheet.com/b/publish?EQBCT=2b402845df734c05bd0b65ef5793fe03

Hope this helps!

Paul Newcome

Give this a try as a single column formula with no helpers needed:

=IF([Working Day]@row + (2 - WEEKDAY([Working Day]@row)) = TODAY() + (2 - WEEKDAY(TODAY())), "This Week", IF([Working Day]@row + (2 - WEEKDAY([Working Day]@row)) = TODAY(7) + (2 - WEEKDAY(TODAY(7))), "Next Week", IF([Working Day]@row + (2 - WEEKDAY([Working Day]@row)) = TODAY(14) + (2 - WEEKDAY(TODAY(14))), "2 Weeks Out", "Due Beyond")))

The above will cover all future dates, but past dates would output "Due Beyond". What would you want to output for past dates?

Dave Mex

Hello @Paul Newcome

Im planning surrounding this in a further IF that checks the status of a task. So for our projects we should never have a "LATE" and if both dates are in the past the task is likely complete. So If I have an IF(Status = Complete, "Complete", "") this should cover these.

However having an option to capture an actual late that may have been missed and have a response as "Late" would actually make sense and help catch errors. So how would I add that too please?

Thank you so much for the help as it does work otherwise.

Dave Mex

@Paul Newcome

=IFERROR(
IF([End Date]@row < [Working Day]@row, "Late",
IF([End Date]@row + (2 - WEEKDAY([End Date]@row)) = [Working Day]@row + (2 - WEEKDAY([Working Day]@row)), "This Week",
IF([End Date]@row + (2 - WEEKDAY([End Date]@row)) = ([Working Day]@row + 7) + (2 - WEEKDAY(([Working Day]@row + 7))), "Next Week",
IF([End Date]@row + (2 - WEEKDAY([End Date]@row)) = ([Working Day]@row + 14) + (2 - WEEKDAY(([Working Day]@row + 14))), "2 Weeks Out", "Due Beyond")))), "ERROR")

This is what Iv gone with and this has worked perfectly! Thank you for all your help with this!

Paul Newcome

Glad you were able to get it sorted. Happy to help. 👍️