Correcting WeekOf and Avg Formulas

Muhammad

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

Mike TV

@Muhammad

If you change your Week Of formula to this it'll output the correct year for the week of.

=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 - 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))))

I'm not yet sure what's up with the Avg formula. I'm getting #UNPARSEABLE with it in a test sheet.

Mike TV

@Muhammad

If you change your Week Of formula to this it'll output the correct year for the week of.

=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 - 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))))

I'm not yet sure what's up with the Avg formula. I'm getting #UNPARSEABLE with it in a test sheet.

Mike TV

However, that just fixes your Week Of formula for Dates Completed that are 2022. If your completed date is 2021 or 2023 it still outputs a 2022 date. I haven't yet gone through the whole formula to see what you're trying to do.

Muhammad

@Mike TV Thank you for the fix! Ideally, I would like for the formula to be able to give an accurate date for any entry (regardless of whether or not the year is 2022, or 2023, or whatever date), maybe there is some way in this formula to pull the year from the date I am parsing? Regarding the avg formula, I don't know why it is returning unparseable for your sheet, on mine it seemed to at least produce an output, but it's fine as I have tried to move to another solution that does not involve calculating the average on the sheet, but rather counting instances on the sheet, and generating the average on a report. This has of course come with its own difficulties, which I was ask in a separate post. Thank you for the help so far by the way, it is greatly appreciated!