I have a sheet with the date of an initial assessment. A review for each assessment needs to be done every 90 days.

This is what the table looks like:

In the second column, the last review will be entered so the next review will also update. However, the review date is based on the initial assessment, not the date of the review. So if the review is done early or late, the next review date will still be based on the initial assessment.

This is what the table should look like

For now I am using this IFS formula in the third column that looks at the second column and updates the date for the next review:

=IFS(B1="",A1 90,(B1>=A1 0)*(B1<=A1 90),A1 180,(B1>=A1 90)*(B1<=A1 180),A1 270,(B1>=A1 180)*(B1<=A1 270),A1 360,(B1>=A1 270)*(B1<=A1 360),A1 450,(B1>=A1 360)*(B1<=A1 450),A1 540,(B1>=A1 450)*(B1<=A1 540),A1 630)

This works perfectly fine, but some reviews can potentially be more than 630 days later. Is there a way to shorten this formula, or will need to keep adding more and more to calculate the dates past 630 days?

CodePudding user response：

You can try with ROUNDUP:

=IF(B1="",A1 90,A1 (1 ROUNDUP((B1-A1 1)/90))*90

That will calculate the amount of 90 days passed and round up to the next one 90s

CodePudding user response：

The formula you've provided is checking for specific ranges of days between the initial assessment and the last review, and then returning the next review date based on those ranges. It's a bit long and can be hard to read.

To shorten the formula and make it more flexible for future assessments, you could use the MOD function to check if the number of days between the initial assessment and the last review is a multiple of 90. If it is, the next review date would be 90 days after the last review. If not, you could add the remainder of days to 90 to get the next review date.

Here's an example of what that formula might look like:

=IF(B1="",A1 90,B1 90-MOD(B1-A1,90))

This formula checks if the last review is empty, if yes it returns the date of initial assessment plus 90 days, otherwise it returns the date of last review plus 90 days minus the remainder when last review date minus initial assessment date is divided by 90. This way, it will always give you the next review date after 90 days of the last review date, regardless of how many days past the last review date is.