Hi all!
I am trying to do 2 operations on some columns I have in a sheet. I have written (with your help ofc!) 2 formulas to fit my needs, these are a week of, and a avg formula. To be more specific, I have a sheet that has tasks on it. Assigned to these tasks are days it took to complete them, as well as the day of completion. I want to generate a report that shows the average days it takes to complete a task per week, and these weeks start with a Friday instead of a Monday or a Sunday. I have written 2 formulas to help me with this, they are the following:
Week of Formula:
=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(2020, 12, 30) + ((IF(WEEKDAY([Engineering Date Completed]@row) = 6, WEEKNUMBER([Engineering Date Completed]@row) + 1, WEEKNUMBER([Engineering Date Completed]@row))) * 7 - 4), 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))))
Avg Formula:
=IF(ISBLANK([Eng. Completed Week Of]@row), "", (AVG(COLLECT([Engineering Days to Complete]:[Engineering Days to Complete], [Eng. Completed Week Of]:[Eng. Completed Week Of], @cell = [Eng. Completed Week Of]@row))))
This sheet contains tasks that have yet to be completed, so their dates for completion are blank, hence why I added the ISBLANK condition, to skip in those cases. These formulas worked fine while I was working with dummy data, but now that I have moved them over to the live sheet I am receiving a few errors in the calculations. I believe the avg, formula is fine (from what I can tell at least), any error that occurs in it I believe is due to the week of formula. An example of an error that occurred is the following:
the date of 06/21/22 was set as completed the week of 06/19/21 (a full year before!). moreover, when looking through the values on a report I generated, I noticed that some weeks actually start on the Saturday instead of the Friday, as planned. I have parsed through this formula for quite some times now, and I am lost as to what I have done wrong. If anyone can help me with these formulas, I would beyond greatly appreciate it! Thank you all in advance!
Best regards,
Muhammad
