Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

TODAY() cannot read date from Created Time field correctly?

Topic Labels: Formulas
Solved
Jump to Solution
125 3
cancel
Showing results for 
Search instead for 
Did you mean: 
n0xp
5 - Automation Enthusiast
5 - Automation Enthusiast

Either I've stumbled upon a great bug or I'm a complete idiot. If history is anything to go by let's assume the latter.

In a greater context I'm using a formula to check if TODAY() is in the range of some dates.

Sometimes the date is from an automatic Created Time field, other times it's from a regular date field.

 

When I check the Created Time (doesn't matter if the field is with out without a timestamp) on a record created moment ago against TODAY() I get a false.

I used it in a long formula, but boiled down to it's minimum it just doesn't work.

 

IF(TODAY()={Created Time},"1","0")

 

The above returns 0 on a record created 15 minutes ago.

If I ask it to check a date field with today's date inserted, then it returns 1.


What's going on and how can I work around this?

1 Solution

Accepted Solutions
Greg_F
8 - Airtable Astronomer
8 - Airtable Astronomer

Hi @n0xp 
The  TODAY() returns today date which will look likely like this:

Greg_F_0-1674467411147.png

Which is technically a string like this 2023-01-23T00:00:00.000Z 

You can wrap both dates in:

DATESTR(Date)
 
this way they will have the same format and take only the Date portion without time. 

See Solution in Thread

3 Replies 3
Greg_F
8 - Airtable Astronomer
8 - Airtable Astronomer

Hi @n0xp 
The  TODAY() returns today date which will look likely like this:

Greg_F_0-1674467411147.png

Which is technically a string like this 2023-01-23T00:00:00.000Z 

You can wrap both dates in:

DATESTR(Date)
 
this way they will have the same format and take only the Date portion without time. 

YES! Thank you very much for the workaround.

But logically that's such a mess.

Is there any reason why this issue shouldn't be fixed to meet the immediate expectation?

Greg_F
8 - Airtable Astronomer
8 - Airtable Astronomer

@n0xp it is just computers being very computers and very correct.

Each Date field in Airtable is technically a Datetime field, so that user can choose abstraction level they want - just the Date or Date with time. There is no pure Date field. Technically - there is also no such thing as pure Date - just humans ignoring the specific time of the day when an event happened.

It is a good thing to remember that underlying data in Airtable can more precise than formatting displayed (same case can be with Number and currency rounding).