Date Manipulation

[Klent]

Good day to all!

I love how the Airtable works, but restricted by a total lack of understanding to some of the formulas. Would any one be able to help me how to figure this problem of mine please?

Apologies if this is a stupid question, but:

I’ve made a table of content for tracking of bug reports.

What I would like to do is when the Status is marked “Completed”. The Completion Date should show the Now() or Today() formula. Else, it should be black.

Here’s the formula I have used:
IF(
(Status = “Completed”),
DATETIME_FORMAT(NOW(),‘MM/DD/YYYY hh:mm’),
‘’
)

However, when I tried doing it. Every time I mark a row with status “Completed”, all the dates in are also being changed.

Thank you!

[kuovonne]

Welcome to the Airtable community!

It sounds like you want to know when a record is marked as complete. Airtable formulas are constantly recalculated whenever any of their inputs change, so using NOW will not “lock in” the time of the change. NOW shows approximately the current time (but may be up to an hour late), depending on if the base is open or not.

Try using LAST_MODIFIED_TIME instead.

IF(
  Status = "Completed",
  LAST_MODIFIED_TIME({Status})
)

You may also need to into the formatting options of the formula field to make sure that you are using the timezone and date format you want.