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

[Chris_Sanders]

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

It would be great to have that working.

[Andrew]

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

[Alexander_Sorok]

Good point indeed! We’ll make it happen.

[Bernard_Toure]

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]

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

[Bernard_Toure]

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]

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

[Liz_Zimmerman]

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]

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.

My formulas are:

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

DATETIME_DIFF({Date lookup}, TODAY(), "days")

[Liz_Zimmerman]

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.