Audit Cadence Formulas for Due Dates

Dawn McCallister

Hello

I am working through a solution that sends notifications to the quality techs when an audit is due. I have been successful with the formulas that capture an audit cadence of Due Monday every week, due every 30 days or 45 days but there is a scenario that has me stumped. Honestly, I am struggling with even where / how to begin …..

Audit A is due on Tuesday and Thursday each week. If the audit was completed on Tuesday and ""completed date" column populates, the next Due date should update to Thursday. If the audit is not completed Tuesday but rather completed as past due on Wednesday, the next audit day is still weThursday. If completed on Thursday then next audit day is Tuesday. Audits can be completed on Saturday but there are no audits due on Saturday.

Any advice or additional questions needed to help find the solution?

Leibel S

I made it a bit more dynamic:

To change any of these options you can update the numbers here (corresponding to the day of the week you want to evaluate:

=[Last Completed]@row + MIN(([Last Completed]@row + 7 - IF(WEEKDAY([Last Completed]@row) = 5, 0, WEEKDAY([Last Completed]@row - 5))) - [Last Completed]@row, ([Last Completed]@row + 7 - IF(WEEKDAY([Last Completed]@row) = 3, 0, WEEKDAY([Last Completed]@row - 3))) - [Last Completed]@row)

You could also store this number in a cell…

Leibel S

@Dawn McCallister

Can you share your sheet columns and how you are tracking this?

Dawn McCallister

@Leibel S

Absolutely, but note I am trying to adjust the current setup. I am not the creator of the current sheet but I am trying to adjust for them so that the sheet meets their needs.

The "Last Completed" looks at a separate sheet, where the audit records are stored, and pulls back the most recent audit date.

=MAX({Record Creation Date}.

The "Due Date" column is a formula that adds the number of days from the "Frequency (Days) to the "Last Completed" date.

=[Last Completed]@row + [Frequency (Days)]@row

The team is asking that instead of the cadence being set by a specified number of days from the last completed to the following scenarios

1 - Monthly audit (this formula I am all set on)

2 - Weekly audit that occurs on the same day of the week (every Monday or every Wednesday…. ) - I believe that I have figured out this solution

3 - Audits that need to be completed 2x a week on Tuesdays and Thursdays. This requirement, I am stumped on.

Dawn

Leibel S

@Dawn McCallister

Does need to be dynamic, i came up with this, you can hard code (by manually calculating) the days that need to be added (depending on which day of the week.

See below formula, let me know what you think

=[Last Completed]@row + VALUE(MID("2101043", WEEKDAY([Last Completed]@row), 1))

Dawn McCallister

@Leibel S

The formula is close, if the "Last Completed" equals a day other than the Tues or Thurs, the formula returns the expected results. If the "Last Completed" equals a Tues or Thurs the formula is returning the same due date as the entered "Last Completed" date. Here are a couple of examples:

I added a testing column "Tues/Thurs" Due Date

Leibel S

So on tuesday or thursday you want it to say the next tue or thur?

Dawn McCallister

@Leibel S - correct

Leibel S

I made it a bit more dynamic:

To change any of these options you can update the numbers here (corresponding to the day of the week you want to evaluate:

=[Last Completed]@row + MIN(([Last Completed]@row + 7 - IF(WEEKDAY([Last Completed]@row) = 5, 0, WEEKDAY([Last Completed]@row - 5))) - [Last Completed]@row, ([Last Completed]@row + 7 - IF(WEEKDAY([Last Completed]@row) = 3, 0, WEEKDAY([Last Completed]@row - 3))) - [Last Completed]@row)

You could also store this number in a cell…

Dawn McCallister

@Leibel S that did the trick … Thank you so much

Dawn McCallister

@Leibel S

The formula worked, but I need help translating it into a statement to understand why it works. Can you walk me through the formula?