Hi…I have a field with ‘arrival date’, a field with ‘terms’ (which is days prior to arrival payment is due. This is a number and varies from record to record)(Both are lookup configurations) and I want the third field to calculate the date it would be if the terms number (such as -3) is subtracted from the ‘arrival date’. I tried DATEADD({arrival date},terms,‘days’) but it didn’t work. I experimented with DATEADD({arrival date},-3,‘days’) and with the ‘terms’ look up field just being an integer instead of the look up and those did work so I am not sure how to do this. Thank you!!
Solved
Due Date formula
Best answer by Justin_Barrett
Emily_Cooley wrote:
Hi Justin…Thank you. It was originally a single select but I changed it to a number thinking that this was the issue as well but through my experimentation, it appears to be a problem with “terms” in my task table being a lookup. It only works if the ‘terms’ in my task table is a # instead of the lookup. I found a workaround that will be fine…I’m becoming a queen of that. I’ll do the dateadd in the groups table instead where the terms is a # and then in the tasks table just make make the due date the lookup. This works just fine, I was trying to not add any more fields to my group table as it is pretty large but I will just hide it. Any other thoughts of why it wouldn’t work if the ‘terms’ field is a lookup?
Thanks,
Sorry. I forgot something tricky about Lookup fields: they return an array when queried by a formula. Even if the associated Link field only points to one other record, the Lookup still returns a single-item array. Some operations in Airtable’s formulas will auto-convert what’s in an array and use it properly, but not all of them, and this is one of those cases.
Here’s what was originally happening. The Single Select field returns a string, even when numbers are used as options, so the Lookup field was returning a single item string array. How DATEADD() read that string array is unknown, but in a quick test, it appears to treat the array—regardless of its contents—like the number 1, and only adds one day to the specified date. Even if the array contains numbers, those numbers aren’t recognized.
If you want to switch back to that Single Select-driven setup, you can make it work by converting the array in {terms} to a single string, then converting that string to a number, like this:
DATEADD({arrival date}, VALUE(terms & ""), "days")Is your {Terms} field (the one where the value is entered, not the one where it’s being looked up) actually a Number field? Or is it a Single Line Text field? If it’s a Single Line Text, I recommend converting it to a Number field, which should solve the problem.
If it’s a Number field, and it’s not working properly, could you please post some screenshots showing more details of your specific setup?
Justin_Barrett wrote:
Is your {Terms} field (the one where the value is entered, not the one where it’s being looked up) actually a Number field? Or is it a Single Line Text field? If it’s a Single Line Text, I recommend converting it to a Number field, which should solve the problem.
If it’s a Number field, and it’s not working properly, could you please post some screenshots showing more details of your specific setup?
Hi Justin…Thank you. It was originally a single select but I changed it to a number thinking that this was the issue as well but through my experimentation, it appears to be a problem with “terms” in my task table being a lookup. It only works if the ‘terms’ in my task table is a # instead of the lookup. I found a workaround that will be fine…I’m becoming a queen of that. I’ll do the dateadd in the groups table instead where the terms is a # and then in the tasks table just make make the due date the lookup. This works just fine, I was trying to not add any more fields to my group table as it is pretty large but I will just hide it. Any other thoughts of why it wouldn’t work if the ‘terms’ field is a lookup?
Thanks,
Emily_Cooley wrote:
Hi Justin…Thank you. It was originally a single select but I changed it to a number thinking that this was the issue as well but through my experimentation, it appears to be a problem with “terms” in my task table being a lookup. It only works if the ‘terms’ in my task table is a # instead of the lookup. I found a workaround that will be fine…I’m becoming a queen of that. I’ll do the dateadd in the groups table instead where the terms is a # and then in the tasks table just make make the due date the lookup. This works just fine, I was trying to not add any more fields to my group table as it is pretty large but I will just hide it. Any other thoughts of why it wouldn’t work if the ‘terms’ field is a lookup?
Thanks,
Sorry. I forgot something tricky about Lookup fields: they return an array when queried by a formula. Even if the associated Link field only points to one other record, the Lookup still returns a single-item array. Some operations in Airtable’s formulas will auto-convert what’s in an array and use it properly, but not all of them, and this is one of those cases.
Here’s what was originally happening. The Single Select field returns a string, even when numbers are used as options, so the Lookup field was returning a single item string array. How DATEADD() read that string array is unknown, but in a quick test, it appears to treat the array—regardless of its contents—like the number 1, and only adds one day to the specified date. Even if the array contains numbers, those numbers aren’t recognized.
If you want to switch back to that Single Select-driven setup, you can make it work by converting the array in {terms} to a single string, then converting that string to a number, like this:
DATEADD({arrival date}, VALUE(terms & ""), "days")Justin_Barrett wrote:
Sorry. I forgot something tricky about Lookup fields: they return an array when queried by a formula. Even if the associated Link field only points to one other record, the Lookup still returns a single-item array. Some operations in Airtable’s formulas will auto-convert what’s in an array and use it properly, but not all of them, and this is one of those cases.
Here’s what was originally happening. The Single Select field returns a string, even when numbers are used as options, so the Lookup field was returning a single item string array. How DATEADD() read that string array is unknown, but in a quick test, it appears to treat the array—regardless of its contents—like the number 1, and only adds one day to the specified date. Even if the array contains numbers, those numbers aren’t recognized.
If you want to switch back to that Single Select-driven setup, you can make it work by converting the array in {terms} to a single string, then converting that string to a number, like this:
DATEADD({arrival date}, VALUE(terms & ""), "days")Ahhhhh…Thanks Justin! That makes absolute sense. I will give it a shot and definitely write that formula in my handy dandy reference book for the next time. You Rock! I have to admit, this is pretty fun to learn. Any good resources for really understanding formulas? Thank you!
Emily_Cooley wrote:
Ahhhhh…Thanks Justin! That makes absolute sense. I will give it a shot and definitely write that formula in my handy dandy reference book for the next time. You Rock! I have to admit, this is pretty fun to learn. Any good resources for really understanding formulas? Thank you!
When I got started with Airtable a little over a year ago, this community was a great resource when it came to learning formulas. Between reading posts and running lots of tests, I was able to get up to speed fairly quickly.
What I’m trying (barely successfully) to do now to give back to the community is sharing specific tips in video form on my YouTube channel, All About That Base. I’ve only got one video posted there so far (not counting my teaser from last fall), but there is a truckload of ideas waiting in the wings, including lots of deep diving into Airtable’s formulas.
Justin_Barrett wrote:
When I got started with Airtable a little over a year ago, this community was a great resource when it came to learning formulas. Between reading posts and running lots of tests, I was able to get up to speed fairly quickly.
What I’m trying (barely successfully) to do now to give back to the community is sharing specific tips in video form on my YouTube channel, All About That Base. I’ve only got one video posted there so far (not counting my teaser from last fall), but there is a truckload of ideas waiting in the wings, including lots of deep diving into Airtable’s formulas.
Thank you Justin. I’ll check it out. Can you take problems that you help find solutions for here and do videos on them (with expansion of course) in YouTube?
Here’s one more: I have 2 fields in my financials table “# picked up” (rooms) and “# guests”. If the # guests is 2x greater than '#picked up" (In otherwords, more than 2 guests per room), I need to add 10 pp. I will create a new field for this information and then add it to my formula to calculate room total .
Thanks.
Emily_Cooley wrote:
Thank you Justin. I’ll check it out. Can you take problems that you help find solutions for here and do videos on them (with expansion of course) in YouTube?
Here’s one more: I have 2 fields in my financials table “# picked up” (rooms) and “# guests”. If the # guests is 2x greater than '#picked up" (In otherwords, more than 2 guests per room), I need to add 10 pp. I will create a new field for this information and then add it to my formula to calculate room total .
Thanks.
Definitely! Several items on my ever-growing list have been inspired by community conversations. Is there something about this discussion that you’d like to see covered in greater depth?
Regarding your follow-up question, am I correct in interpreting “add 10 pp” to mean that you need to add $10 per person to their total booking rate? If so, this formula should do what you want:
IF({# guests} > ({# picked up} * 2), 10 * {# guests}, 0)
However, that adds $10 for every person in the party, not just the extras. If the $10/person should only apply to the extras, then use this:
MAX(0, {# guests} - ({# picked up} * 2)) * 10
1 person likes this
I hope that reviving this is okay because it's the same issue I'm having, except I'm having trouble getting the resulting formula field to format the date in a way that doesn't include the time.
I have a date field for when a contract expires, and a number field for how many days in advance I want to be reminded to renew, and I'm using the below formula:
DATEADD({Expiration Date}, -{# of Days to Remind}, 'days')The result is the correct date, but it includes a time even though the given Expiration Date field does not. Can someone help me to remove the time so only a date is returned?
Also, is there a way to suppress "#ERROR" responses for fields where the formula can't run properly? I'd much rather just have an empty field than errors because some won't have renewal dates.
Thanks!
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.