Trying to add a looked-up value and DATEADD

[Duncan_Fischer]

I am trying to a similar thing, but instead of adding an INTERGER for the multiplier in the DATEADD formula, I am trying to pick up a value form a table LOOKUP.

Have also tried with a FORMULA cell and it doesn.t work either.

I have different categories of appointments to book, that each have different duration and priorities

TIA,
Duncan

[FredK_Trashmail]

Hi,

DATEADD( ThatDate, VALUE(lookup value), ‘day’) shows #ERROR but DATEADD( ThatDate, lookup value +0, ‘day’) works for me.

Cheers

Fred

[Larrissa_Cunnin]

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?

[Larrissa_Cunnin]

Just after I posted this I found the answer over here:

How do I add a set number of days to a date? Views

From your description, it sounds like the {Days to Maturity} field is a Lookup of a number field. Because Lookups can contain multiple linked values, in formulas they’re represented as an array. Sometimes Airtable’s formula engine will automatically recognize and convert these arrays, but in other cases like DATEADD you have to manually convert from an array to a single value. ARRAYJOIN will convert from an array to text, and VALUE will convert from text to number. Try this modification: DATE…

[A_R]

Elias_Gomez_Sainz:

The only way to get it to work is summing with a 0 : DATEADD(Date, Lookup + 0, 'days') . Note the + 0 .

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?

[Elias_Gomez_Sai]

I don’t work at Airtable :man_shrugging:

[A_R]

I know. But I thought that, since Airtable support has already been mentioned and the big is real, AT staff would be actively participating in this thread.

Naive me?

[Karlstens]

Such a strange bug, but glad the workaround works. Any chance that this is going to be addressed?

[Ruslan_Romaniuc]

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!

[Ruslan_Romaniuc]

@Elias_Gomez_Sainz @W_Vann_Hall can you please suggest if there’s any solution to my need?
Thank you

[Vert_La_Terre]

thank you for the solution! I was getting crazy other this simple “bug”.