Can I use variables in formulas


#1

I want to put the result of another field in place of [#] in the formula: DATEADD([date], [#], ‘units’)
I’ve tried enclosing the field name in [] {} () but nothing seems to work.

Am I missing something?


#2

Usually you don’t need 'm (only use when there are {spaces your field name}). And when you use the formula editor it will fix that for you. Simply pick the field from below and narrow it down by typing the first character(s) from the name.

Also, it shows examples in the editor. :slight_smile:


#3

Thanks Tuur, I probably should have been more complete in my description: As well as trying [], {} and (), I tried it with just the field name, but to no avail. The only time this formula worked was if I inserted a specific number for [#]

I’ve had success using variables in other formula, just not this one:
DATEADD([date], [#], ‘units’)


#4

Ah. In that case it could be a specific date related conversion thingy. :slight_smile:

I’ll have a closer look at it later…


#5

No probs here when I use…

DATEADD(Datefield, Numberfield, 'day')

#6

Thanks for testing this Tuur - I also keep trying but this formula field only returns the same date as the [Datefield] with an additional (unwanted) time stamp of 12:00am.

https://airtable.com/shrX2oVgmvKcDyXfE
The formula in NextDateDue field reads: DATEADD(JobDate, Frequency, ‘day’)

:slight_smile: It’s going to be fun when we get to the bottom of this one :slight_smile:


#7

What happens when you change the format of {frequency} to drop the decimal point & zero?


#8

Oh. Now I see that the field is not a number field. As lookups are handled differently (as arrays) that could be the problem. :slight_frown:


#9

Yes, apparently the DATEADD formula will not accept a number from a Lookup or a Formula ONLY a Number field :frowning:

This is unfortunate, as the number I am wanting to reference is from another table. The only way around this is likely to involve copying from the lookup and pasting into a number field for every new record.


#10

And what happens when you use the {freq} field?


#11

That field works, as long as it’s a number field only - as I edited in the above reply, it seems copying from the lookup and pasting into a number field for every new record is the only solution.

Any idea for how to remove the extra time format in the result of this DATEADD formula?


#13

Yup. Simply use an extra DATETIME_FORMAT. :slight_smile:

Also, I came up with a possible workaround. Please try a rollup field instead of the lookup.


#14

Awesome - Rollup and DATETIME_FORMAT all works beautifully now - thanks a bunch for that workaround! :slight_smile:

(from down under in New Zealand)


#15

My pleasure. No worries! :wink: