Static date function in IF()

[Nicola_Pilcher]

Hi All, I am trying to get a function to enter the current date when I change the status to done but I want the date to stay static and I cant figure out what date function to use for this. Here is what I have:

IF(Status=“Done”, DATETIME_FORMAT(TODAY(),“DD/MM/YYYY”), BLANK())

Now when I changed one item to “Done” yesterday it put in yesterdays date but when I logged in today it had todays date instead. I figured this would happen but don’t know what to put where I have “TODAY()” to get this to work.

Can anyone help me with this?

[Databaser · Accepted solution]

HI @Nicola_Pilcher and welcome to the community!

As far as I know, you will have to use and automation for this.

New field: last modified > specified for your status field

Trigger: when a record matches conditions (status = done)
Action: update record (new date field = date in “last modified” field")

That way, you get a static date when you change the status to “to do”.

[kuovonne]

Databaser:

New field: last modified > specified for your status field

Why not have just the last modified time field without the automation? If you need the field to be blank when the status is not done, do that with a formula field that shows the last modified time only when the status is done.

[Nicola_Pilcher]

Thank you @Databaser I didn’t think to do that

[Nicola_Pilcher]

I add nots into certain items after they have been completed so a general last modified wouldn’t work here. But thanks anyway.