If and Today Fuction

New Contributor
DateStatus
12/05/2022Completed
12/05/2022Rescheduled
29/03/2022Booked

 

I am trying to make the date regenerate 90 days from todays date, whenever the status is marked "Completed". 

 

I have tried to do this with the IF function, but I an struggling with it. 

 

Is there a way to do this? - Thanks in advance

3 Replies

@Sulim99 

=IF(B2="Completed",TODAY()+90,"")

Is this what you want to do?

if and today formula.JPG

That's perfect - however, if I change the status to "booked" after this, the regenerated date dissapears.

Is there a way to keep this Regenerated date once done, and also change the status?

I am trying to use this to be able to continiously review, book in and complete a task - so I am wanting to be able to generate the new date, but then also book it in and then complete it once more to generate over and over.

I hope i've explained sufficiently - thank you for your help.


@Sulim99 

This is probably with the worksheet change event. In the attached file you can enter "Completed" in any cell in range B2:B100 and todays date + 90 days will be entered in the adjacent cell. The date will remain if you change the cell in column B from "Completed" to "Booked" or else. However if you enter "Completed" again tomorrow the date in column C will be updated.