Skip to main content

DATETIME_FORMAT or SET_TIMEZONE formulas not working with a lookup field

  • April 17, 2024
  • 3 replies
  • 30 views
M
Forum|alt.badge.img

Hello,

The following formula doesn't work, with the field {Days (from Boosts)} being a lookup field representing the number 30. Is there an issue with using lookup fields within the DATEADD formula?

 

DATETIME_FORMAT(SET_TIMEZONE(DATEADD({Created},{Days (from Boosts)},'days'),{Tutor Timezone (from Tutor)}),'DD MMM YYYY')
 
The formula above works if I replace {Days (from Boosts)} with the number 30 however. I've just done some more testing and the following formula works as intended:

DATEADD({Created},{Days (from Boosts) formatted},'days')
So I believe the issue lies with DATETIME_FORMAT or the SET_TIMEZONE functions

Any help would be appreciated.
 
Thank you,

3 replies

TheTimeSavingCo
Forum|alt.badge.img+31

If you could provide a read only link with example base and example data it would be very useful in helping you troubleshoot this issue!

Adam, Airtable Consultant - thetimesaving.company
ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • April 17, 2024

@Mitchell_Knox 

The reason it’s not working is because lookup fields are arrays, not numbers nor strings of text.

If you’re certain that your lookup field will always contain one value only (which is what’s needed in your situation), then you can turn your lookup into a string of text like this:

{Your Lookup Field} & ""

That might be enough for your formula to work, but if that doesn’t work, then you can often turn text into numbers with this formula:

VALUE({Your Lookup Field} & "")

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld 

jsep
Forum|alt.badge.img+9
  • jsep
  • Participating Frequently
  • April 17, 2024

Try to use this formula:

 

 

DATETIME_FORMAT( SET_TIMEZONE( DATEADD({Created}, {Days (from Boosts)} & "", 'days'), {Tutor Timezone (from Tutor)} & "" ) ,'DD MMM YYYY' )

 

 

 Explanation:

Lookup fields are array values, so for this formula convert them into strings will do the trick. 

This is done by adding & "" to the formula

 

 

{Days (from Boosts)} & ""

 

 

I hope this helps! If you need help implementing this or any other solution, feel free to schedule a free call with me.

- Juan, code and no-code solutions expert

Terms & ConditionsAccessibility statement