Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

Date and Time Functions Functions work with dates/times that appear in Lookup field

cancel
Showing results for 
Search instead for 
Did you mean: 
Chris_Sanders
6 - Interface Innovator
6 - Interface Innovator

Attempts to use Date and Time functions with a date in a Lookup field result in #ERROR!

Screen Shot 2016-01-06 at 8.06.12 AM.png

It would be great to have that working.

9 Comments
Andrew
6 - Interface Innovator
6 - Interface Innovator

Good point. Copying @Alexander_Sorokin who built our date formulas :slightly_smiling_face:

Alexander_Sorok
6 - Interface Innovator
6 - Interface Innovator

Good point indeed! We’ll make it happen.

Bernard_Toure
6 - Interface Innovator
6 - Interface Innovator

Hi @Chris_Sanders ,
Have you found a workaround for this?
In my case I’m trying to rollup on a max(values) for date column which is a lookup value result without any success. The rollup returns 0 whatever I do.

Cheers

Chris_Sanders
6 - Interface Innovator
6 - Interface Innovator

I have not. The last time I checked, it still gives the error. :disappointed:

Bernard_Toure
6 - Interface Innovator
6 - Interface Innovator

Just found a solution in my case.
My schema is:
Table 1 with the Date field.
Table 2 linked to table one and lookup the date from Table 1.
Table 3 linked to Table 2.

In Table 3 I need to lookup the Table 1 Date value, but through the Table 2 as I don’t have link between Table 3 and Table 1.

What I did is:
In Table 2, add a column that rollup the date from Table 1, so it brings back the same value that is in the lookup field (only way I found to have a valid date format in Table 2).
Then calculate the number of days between a date (say ‘1970-01-01’) and the rollup result and put that number of days in a dummy column.

In Table 3 rollup the value from the dummy column in Table 2 (max Number of day since 1970).
Then add this number to 1970 to get the final date in Table 3.

Seems to be working for me.

Cheers,

entropic
6 - Interface Innovator
6 - Interface Innovator

Has there been any update on this? It’s been over a year since the last Airtable response…

Liz_Zimmerman
4 - Data Explorer
4 - Data Explorer

Checking in on this! Almost 4 years since the original posting, and I’m in need of this function now. Any update, @Alexander_Sorokin??

Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @Liz_Zimmerman! :grinning_face_with_big_eyes: Alexander isn’t an Airtable employee. The commenter before him (Andrew) is.

That aside, I just ran a test, and it works for me.

Screen Shot 2019-10-22 at 8.53.01 PM.png

My formulas are:

DATEADD({Date lookup}, 3, "days")

DATETIME_DIFF({Date lookup}, TODAY(), "days")
Liz_Zimmerman
4 - Data Explorer
4 - Data Explorer

Got it - I was just flagging for the person who said they would make it happen.

Thanks for the response. I spent so much time on this yesterday and found a workaround, and just now I’ve gone back in to try the basic formula (that was giving me trouble yesterday) and it seems to be working. Honestly, I’m totally dumbfounded. I must have glossed over something simple yesterday that ended up fixing it… who knows! Anyway, thanks for checking in.