Airtable - jeez! they make it difficult!

You can do that with an extra formula, but the much easier solution would be to just create a field that is a “Last Modified Time” field type, and have it monitor the status field.

Also, since you’re relatively new to Airtable, you might benefit from taking my free Airtable training course, which you can take for free by signing up for a trial membership with LinkedIn Learning: https://www.linkedin.com/learning/learning-airtable/ 

I don't think we can do what you need with just a formula field I'm afraid; we'd  need an automation to tie it all together

Try the following:
1. Create a "Last Modified Time" field called "Summons Status last modified" or something and point it at the "Summons Status" field
2. Create a "Date" field called "Summons Status is 'Received' Date" or some such
3. Create an automation that will trigger when the "Summons Status" value is "Received"
4. Give that automation an "Update record" action, and its action will be to update the triggering record and it'll take the value in the "Summons Status last modified" and paste it into the "Summons Status is 'Received' Date"

"TODAY()" is always going to show the current date, so, while it looks right today, tomorrow if we looked at your base it'd show tomorrow's date, which I'm assuming is not what you're looking for

The reason you're getting "23-07-31T00.00.00.000Z" is because your formula is returning an empty string ("") when the "Summons Status" value is not "Received", and as a result your formula field's type is actually a string, and Airtable displays date values in string values in the manner above.  Try modifying your formula to be:
`IF({Summons Status}="Received",TODAY())`

Now, you should see an actual date formatted the way you expect, and if you edit the field and go to "Formatting" you should be able to change the date formatting as well

Best of luck with everything man

No, Adam’s advice above is overkill and won’t yield the correct solution anyways.

All you need is the last modified time field, like I said.

However, if you want to automate this with formulas (which is completely unnecessary in your case), you would need to use Airtable’s DATETIME_FORMAT function. 

Thanks for your input - and I will have a look at your training link!

However, while a simple solution I don't think it gives me what I want? I only want to record the date if the stars is "Received", and not any other status.  Basically it is recording the date an item was received.  As far as I can see your solution will input the date whatever status is selected?

Hi, 

Thanks for your input.  I have tried several times to use Automation but the options are confusing and I have not yet managed to get it to work.

Please see my response to your answer above.

Geoff,

There are several things going on in your post.

1. You have prior experiences with databases that are probably getting in your way as you learn Airtable. Airtable does not handle relationships in the same way as other traditional databases. Airtable's formula language is also different from other systems.

2. The formula TODAY() returns the current date with a time of GMT midnight. However, that is not the problem with what you see. As @TheTimeSavingCo  stated, your formula is converting that date into a text string because the "else" part of your IF() is an empty text string. That forces all output of the formula to be a text string, so you are seeing the ISO format of today's date. If you remove the empty text string from your formula, you will be able to format the result as a date.

IF({Summons Status}="Received",TODAY())

3. As @ScottWorld mentioned, you probably want the last modified time of the field, and not TODAY() because the value of TODAY() will change with every day that passes.
IF({Summons Status}="Received",LAST_MODIFIED_TIME({Summons Status}))

Note that if you want to store the date the summons was received even after the summons moves to the next stage, you will need need an automation similar to what Adam stated.

@kuovonne's #3 solution is the simplest and most straightforward. A more complex & probably unnecessary solution would be to create an automation which puts that modified date into another date field whenever the status is changed to "Received". The only advantage to this would be if you wanted the date to show up in an editable date field instead of a formula field.

Thanks for your input.

1. Yes, you are correct, I am used to dealing with database relations in a completely different way, which is why it intrigued me in the first place.

2.  Yes, I realised I was using the wrong function.  You are correct that I want a fixed date following the change of status to 'Received'.

3.  I can understand this formula apart from the very end "Last_modified_time({Status}) - what is "status" in this sense?

Thanks!

I will try the Kuvonne solution and report back. Thanks.

OK - I now have a field called "Summons Received Date with the formula

Yeah, it can get kind of confusing.  I've set something up here for you to check out, and you should be able to view the automation itself to replicate it

Sorry for the delay in responding.  Thank you for your example.  However, I cannot update it or run the automation so I have no idea what it does or who it related to my problem?

Thanks,

Geoff

Hi Geoff, yeah, that automation is set up in my own base so you can't update it or run it.  It's set up as an example of how to solve the problem (I think!) you're having, and you can view the automation and how it's set up so that you can build it in your own base

Apologies if it doesn't solve your problem!

Hi all,

Many thanks for your help with all of this, but I'm afraid I have gotten too frustrated with the shortcomings of Airtable to do even a simple task - something that would have taken me 5 minutes in my usual database development platform.

Maybe it's my inexperience with Airtable and perhaps I'm not using the right tools, but the Airtable Help System is not the easiest to use and it is difficult to find help when you need it, to solve specific issues.

My simple task was to create a table that tracked the receipt and disposition of certain documents.  I had a Status field that was a single select dropdown of "Not Received", "Received", "Awaiting Amendment" and "Processed".  I wanted to record, in another field,  a one-time date when the document was "Received" (when the Status was changed to "Received") and wanted this date to remain, even when changing the status to something else. 

It appears, from my efforts, and those who have assisted on this post, that this is not possible. I am very disappointed and had hoped to be able to make use of Airtable, but it seems not.

Thanks all for your help.

Geoff