SWITCH function for a date field ?

[HaraldPalma]

Hello,
sorry, if I ask a question that might have been asked before. I am creating a simple private bookkeeping table and would like to build a SWITCH function that stamps the DATE when a value changes ("paid" / "open").

Furthermore, I have tried the TODAY() function, but it updates every day. The LAST_MODIFIED_TIME() works, but if I change the value of the field, it will automatically update again. I would like to build a function that stamps the date only once, after the value "paid" / "open" changed for the first time.

This is the formula I used so far:

SWITCH(Status, "bezahlt", LAST_MODIFIED_TIME())

Thank you very much!

[kuovonne]

You can limit the LAST_MODIFIED_TIME() to look at only a specific field by including that field name in the function call. 

IF(

  {Status} = "bezahlt",

  LAST_MODIFIED_TIME({Status})
)

However, as long as you are using a formula field, the value of the formula will change whenever any of its inputs change. If the {Status} field changes again, it will have a new value.

if you want to lock on the first time the status field has the "bezahlt" value, you need an automation that puts the time in an editable date/time field. 

[OpsDir_Cassie · Accepted solution]

Hi there! There may be a couple ways to do this. One is to adjust the field type to "Last modified time" and then choose the "specific fields" option, so it only updates this date field when you change one of the selected fields: 

The other way (I think) would be to create an automation. You'd create a separate date field that is labelled with whatever date title you want to show up here. Then the automation would be based on when a record matches certain conditions, it enters today's date into the new field (it would be a dynamic field option). 

However, you'd have to be clear with any other collaborators not to change that "paid/open" status after the initial update. 

Hope this helps.