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.
+12DATEADD 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).
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).
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.
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
