Trying to add a looked-up value and DATEADD

It’s the same, you just change the source of the value from a number to the name of your field:

DATEADD({Date Field}, {Lookup Field}, Unit).

I’ve tried various fields and they don’t compute.

This works, but i need to look up a number of minutes from the separate table of APPT TYPE.

Duncan

You’re right, I’m testing it and I am getting weird behaviour:

• If I make a formula like Lookup + 5, I get a number, so the Lookup field acts like a number.
• If I use it in the DATEADD function it does not work :dizzy_face:
• Using VALUE(Lookup) does not work
• The only way to get it to work is summing with a 0: DATEADD(Date, Lookup + 0, 'days'). Note the + 0.

I think this is a #bugs to note to @Airtable_Support

thanks, that worked. I guess it is a simple bug
D

A naked Lookup is returned as an array — and predicting how Airtable will treat an array when used in a formula is definitely a black art. Fortunately, you can always cast a Lookup into a string by appending ‘&''’ – that is, an ampersand followed by two quotes signifying an empty string — which can then be used in VALUE() or similar functions.

You could also define {Appt Finish} as a Rollup field using an aggregation function. To do so, configure it as a Rollup that follows the link given in {Appt type} to roll up the {Nbr of Minutes} [or whatever you call it] field. Then, in the place where you would ordinarily enter an aggregation function, enter

DATEADD(
    {Appointment Time},
    VALUE(values&''),
    'minutes'
    )

values is a keyword indicating Airtable should use the rolled up value of the specified remote field. It, too, is returned as an array, so it has to be cast as a string and then converted to a number using VALUE().

Why might you want to do this? Well, in this case you aren’t saving very much processing cost or eliminating much complexity. However, it does reduce your field count by one, in that you no longer need explicitly to look up the ‘minutes’ value and then use it in a formula field, as the remote reference and the formula both take place in the same field.

A naked Lookup is returned as an array — and predicting how Airtable will treat an array when used in a formula is definitely a black art. Fortunately, you can always cast a Lookup into a string by appending ‘&''’ – that is, an ampersand followed by two quotes signifying an empty string — which can then be used in VALUE() or similar functions.

You could also define {Appt Finish} as a Rollup field using an aggregation function. To do so, configure it as a Rollup that follows the link given in {Appt type} to roll up the {Nbr of Minutes} [or whatever you call it] field. Then, in the place where you would ordinarily enter an aggregation function, enter

DATEADD(
    {Appointment Time},
    VALUE(values&''),
    'minutes'
    )

values is a keyword indicating Airtable should use the rolled up value of the specified remote field. It, too, is returned as an array, so it has to be cast as a string and then converted to a number using VALUE().

Why might you want to do this? Well, in this case you aren’t saving very much processing cost or eliminating much complexity. However, it does reduce your field count by one, in that you no longer need explicitly to look up the ‘minutes’ value and then use it in a formula field, as the remote reference and the formula both take place in the same field.

Any thoughts on…
I have, in some cases, multiple collaborators that can perform a certain appt type, in the {Appointment Book} i want to limit a field to allow only ONE collaborator to be selected dependent on the appt type.

Thanks so much @W_Vann_Hall … only had to change the minutes to seconds as the original table outputs seconds it seems. One less column to squeeze on the screen.

D

And even if you aren’t significantly reducing processing load in this instance, the use of aggregation formulas in Rollup fields is a good trick to keep in mind because at times it can greatly streamline computation-intensive Lookup references. The case I most often site is that of my data deduplication routines: Switching to an aggregation formula-based algorithm for version 2 seemingly cut overhead on a 1,000-record table by the equivalent of four copies of War and Peace as compared with version 1…

Hi,

I am trying to something similar I think, I have tried all of the above but not working

I am trying to add a unique “feed frequency” to auto calculate the “Next feed date” and using the DATEADD formula is not working for me.

This is what I was trying: DATEADD({Last Feed Date}, {Feed Frequency}+0, ’day’)

Any suggestions?

You’re right, I’m testing it and I am getting weird behaviour:

• If I make a formula like Lookup + 5, I get a number, so the Lookup field acts like a number.
• If I use it in the DATEADD function it does not work :dizzy_face:
• Using VALUE(Lookup) does not work
• The only way to get it to work is summing with a 0: DATEADD(Date, Lookup + 0, 'days'). Note the + 0.

I think this is a #bugs to note to @Airtable_Support

Until the bug is solved, that should be stated in the help articles themselves! That info shouldn’t be here and here alone.

BTW, how’s that bug solving going? It’s been at least a year and a half already?

I don’t work at Airtable :man_shrugging:

Hi there!

I try to use DATEADD to calculate customer “Due Payment Date” based on the order “Ship Date” and picking up values (LOOKUP) from another table since I work with various payment credit terms: 30d, 45d, 50% in 30d / 50% in 60d…

I use positive Integer numbers for the payment terms (30, 45…) and the formula works fine:

DATEADD({Ship Date},VALUE(ARRAYJOIN({Terms 1}),‘days’))

However I have cases with split payment terms like 50% before Ship Date and 50% in 30 days after Ship Date. As said above the formula for “after Ship Date” works with positive Integer lookup values, and I’m struggling to make the same formula work for the advance payment (50% before the Ship Date) - I tried to use in the Lookup table negative Integer number for advance payments. But the problem is the #ERROR in the DATEADD formula whenever I use zero or negative value in the Lookup table instead of positive integer.

Can anyone help please?

Also if anyone knows a way how to manage due dates for split payment terms (for ex. 30/60/90/120 days for one order) without creating separate columns for each payment term and % value it would be of great help!

You’re right, I’m testing it and I am getting weird behaviour:

• If I make a formula like Lookup + 5, I get a number, so the Lookup field acts like a number.
• If I use it in the DATEADD function it does not work :dizzy_face:
• Using VALUE(Lookup) does not work
• The only way to get it to work is summing with a 0: DATEADD(Date, Lookup + 0, 'days'). Note the + 0.

I think this is a #bugs to note to @Airtable_Support