Data from Lookup Field does not suffice as Count in DATEADD

[Andrew_Enright]

Hello,

This works fine: DATETIME_FORMAT(DATEADD({Field Name}, -167, ‘days’), ‘L’)

But this does not: DATETIME_FORMAT(DATEADD({Field Name}, {Lookup Field}, ‘days’), ‘L’)

Will DATEADD just not work if the count variable is not a hard-coded number?

Please help; thank you!

A.

[Andrew_Enright]

I guess a better way to put this is:

What qualifies as a “count unit”?

Thanx,

A.

[Matt_Bush]

DATEADD works with any numeric value, including variable numbers from other fields, in addition to hard-coded numbers.

The problem you’re encountering is that a Lookup field is an array of numbers, not a number itself. The reason it’s an array is that if there are multiple linked records, the Lookup field needs to be able to hold multiple numbers.

To get around this, use an aggregation function in your formula to turn the array into a number. For instance, replacing {Lookup Field} with SUM({Lookup Field}) or MAX({Lookup Field}) will result in the number you want (assuming only 1 value in the lookup array).

[Andrew_Enright]

Got it. And THANKS!

DATETIME_FORMAT(DATEADD({Input Field}, SUM({Lookup Field 1}) - (SUM({Lookup Field 1}) * 2), ‘days’), ‘L’) is working perfectly.

Interestingly though, it calculates one day less than Google Sheets, but identically to timeanddate.com.

A.