How to use UPDATEIF() on the calculated field?

Support
1

Hi everyone,
I can’t figure out a way to make the UPDATEIF() formula works in a field that already has a formula.

For example, I have the date field called [End Date] and it is calculated using this formula WORKDAY([Create Date],15). Now I want the [End Date] to update to the current date when the [Status] is updated to “Close”.

So my ideal formula is [End Date] = UPDATE IF [Status] == “Close” else WORKDAY([Create Date],15), but it doesn’t seem like I can write the else statement in the UPDATEIF formula.

Any suggestions?

Thank you so much
Jill

2

Hi,

Since TODAYTZ() would be triggered each time a recalculation occurred, I would suggest referring to below workaround.

  1. Create a field A1 and add an Update Value button to update status to “Close” and variable {{TODAY}} to field A1.

  2. Apply conditional formula to field End date, which might be IF(Status Field.RAW=“Close”,A1,WORKDAY()).

When status is “Close”, it will return the action date, otherwise, it will be WORKDAY().

3

Thank you

4

Hi Hank,

I have tried the solution you suggested, but the End Date field formula didn’t work.
With minor tweaks based on your suggestion, I was able to make it work without creating a new field.
I set my End Date formula as “WORKDAY(Create Date,15)” and created an Update Value button to update the Status to “Close” and End Date to “{{TODAY}}”. This way, when i create a new item, the End Date is automatically set as 15 days from the Create Date, and when I click the “Close” button, the End Date is updated to today’s date. :slight_smile:
Thank you so much for your help.

1 Like