Airtable - jeez! they make it difficult!

[Geoff_Warburton]

Absolute Beginner here - still struggling to make Airtable do what I want. So here I am - ONE YEAR later after my first post here, and I am not much further forward in my use and comprehension of Airtable.

Admittedly, I have not been continuously attempting to learn Airtable in all that time, but the interface really intrigues me and all the examples make it look really easy to master and the fact is - it isn't!  Being a retired database developer, I am not exactly new to the concept!

However, I have just spent an hour trying to get a formula in an Airtable base record that gives me what I want - simple - you would think, but no - not for me!

All I am trying to do is insert today's date when a status field in the record changes to a certain value.  I've learned that I can use the function today() in a formula field to achieve this. According to the "reference material", today() only returns a date - Not so as far as I can see!  I cannot find any definition description of the function today() to discover what I'm doing wrong!

My formula is 

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

The result I get from this is

23-07-31T00.00.00.000Z

How I go from the above value to a UK version of today's date, 23/07/2023, completely escapes me and is incongruous with the explanation that today() only returns a date (with no time component).

Any help would be appreciated before I once more get so frustrated I give it all up and wait another year before I try again.

Thanks,

Geoff

[ScottWorld]

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/ 

[TheTimeSavingCo]

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

[ScottWorld]

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. 

[Geoff_Warburton]

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?

[Geoff_Warburton]

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.

[Geoff_Warburton]

Please see my response to your answer above.

[kuovonne]

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.

[ScottWorld]

@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.

[Geoff_Warburton]

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!