Recording Week Tasks Were Completed

Muhammad

Hi all!

I'm back with an old problem that rattled me a few months ago. Basically, when a task is completed, its date of completion is saved. For the sake of being able to a process this data in a more comprehensive manner, we want to take that date, and assign it a "weekOfCompletion". Another note, is that the weeks here would be counted by Fridays and not Mondays (so a week is considered from Friday to Thursday).

All that said, with pointers from you guys, I was able to come up with a temporary fix, however, as the year has changed, the formula is now broken. I'm at a loss as to how to fix it, I tried incrementing the years, however, all this did was take record from last year, and assign them to 2023. if any of you could help me fix this or point me in the direction of how to go about doing such, it would be greatly appreciated. Please find the formula below:

=IF(OR(ISBLANK([Engineering Date Completed]@row), NOT(ISDATE([Engineering Date Completed]@row))), "", (IF((IF(WEEKDAY([Engineering Date Completed]@row) = 6, WEEKNUMBER([Engineering Date Completed]@row) + 1, WEEKNUMBER([Engineering Date Completed]@row))) > 19, DATE(2021, 12, 30) + ((IF(WEEKDAY([Engineering Date Completed]@row) = 6, WEEKNUMBER([Engineering Date Completed]@row) + 1, WEEKNUMBER([Engineering Date Completed]@row))) * 7 - 6), DATE(2022, 1, 4) + (((IF(WEEKDAY([Engineering Date Completed]@row) = 6, WEEKNUMBER([Engineering Date Completed]@row) + 1, WEEKNUMBER([Engineering Date Completed]@row))) - 1) * 7 - 4))))

As an example, it would take a task completed on Tuesday December 20th 2023 (12/20/22), and it would output the week of completion as 12/16/22

Thank you all in advance, this community is the best and so helpful!

Paul Newcome

Try this:

=Date@row + (6 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 6, 7, 0)