Formula - Display Tuesday of Every Week based on Submitted Date

JD_425

I currently have a weekly formula setup where we receive requests throughout the week and the submitted date displays in the Date Submitted to DC column (that meet the criteria of LI and NIR in the SMCC Action column) which get submitted on a weekly basis every Monday which displays in the week of column.

In the example below the week of 7/22 (Monday) date means a request will be submitted with any of the dates in the date submitted to DC column from 7/22 (Monday) through 7/28 (Sunday) and will have the week of date of 7/22. The week of date will not flip to the next week of 7/29 (Monday) until requests start coming in starting in from 7/29 (Monday) through 8/4 (Sunday), etc. The current formula below works great with no issues but now want to change the week of from every Monday to Tuesday. Can't seem to get it work.

Would appreciate if someone could re-work the formula so the week of date changes on every Tuesday. For example: Requests submitted with a date in the Date Submitted to DC column from 7/23 (Tuesday) through 7/29 (Monday) would fall in the week of 7/23 (Tuesday) and so forth.

Current Formula:

=IFERROR(IF(OR([SMCC Action]@row = "LI", [SMCC Action]@row = "NIR"), [Date Submitted to DC]@row + (2 - WEEKDAY([Date Submitted to DC]@row)) - IF(WEEKDAY([Date Submitted to DC]@row) = 1, 7, 0), ""), "")

AravindGP

Hi @JD_425

This should work.

=IFERROR(IF(OR([SMCC Action]@row = "LI", [SMCC Action]@row = "NIR"), [Date Submitted to DC]@row + (3 - WEEKDAY([Date Submitted to DC]@row)) - IF(OR(WEEKDAY([Date Submitted to DC]@row) = 1, WEEKDAY([Date Submitted to DC]@row) = 2), 7, 0), ""), "")

JD_425

@AravindGP

AravindGP

Hi @JD_425

You can use the below formula to change the week of from Monday to Tuesday.

=IFERROR(IF(OR([SMCC Action]@row = "LI", [SMCC Action]@row = "NIR"), [Date Submitted to DC]@row + (3 - WEEKDAY([Date Submitted to DC]@row)) - IF(WEEKDAY([Date Submitted to DC]@row) = 1, 7, 0), ""), "")

JD_425

Hi Aravind,

Thank you so much for the information! The week of date changed to Tuesday which is great! However, it changes to the new Tuesday on Monday instead of Tuesday. Example below week of column changed to the next Tuesday 7/30 if a request was submitted with the date of 7/29 in the date submitted to DC column. Would like the week of date stay with the Tuesday of 7/23 for Monday and not change to the next Tuesday until Tuesday date of 7/29 is in the date submitted to DC column.

If possible, would like the week of date in this example to remain at 7/23 with submitted dates in the Date Submitted to DC column from 7/23 (Tuesday) through 7/29 (Monday) and change to the new Tuesday 7/30 on 7/30 Tuesday. Any help would be appreciated!

JD_425

@AravindGP

AravindGP

Hi @JD_425

This should work.

=IFERROR(IF(OR([SMCC Action]@row = "LI", [SMCC Action]@row = "NIR"), [Date Submitted to DC]@row + (3 - WEEKDAY([Date Submitted to DC]@row)) - IF(OR(WEEKDAY([Date Submitted to DC]@row) = 1, WEEKDAY([Date Submitted to DC]@row) = 2), 7, 0), ""), "")

JD_425

Hi Aravind,

Perfect! That did it! Thank you so much as greatly appreciated!

JD_425

@AravindGP