Skip to main content
Solved

Date Calculation Formula

  • January 23, 2023
  • 6 replies
  • 50 views
K
Forum|alt.badge.img+3

I am trying to create a formal that adds a specific amount of days to a date field. The amount of days varies and is pulled using a lookup field. Initially, I was trying to use the DATEADD function but it doesn't seem to work when the added value is another field instead of a numeric value.

The formula I have currently is DATEADD({Submission Date},{Health Plan SLA},"days")

The field Health Plan SLA is a lookup field that pulls a number from another tab. I need this number to be what the formula uses. 

Best answer by TheTimeSavingCo

Could you try the following out?  I think it's down to the format that lookup fields display data in, so we convert it to a string and then into a number, and it seems to work?

DATEADD( {Submission Date}, VALUE(CONCATENATE({Health Plan SLA})), 'days' )
1 Attachments

6 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Could you try the following out?  I think it's down to the format that lookup fields display data in, so we convert it to a string and then into a number, and it seems to work?

DATEADD( {Submission Date}, VALUE(CONCATENATE({Health Plan SLA})), 'days' )
Adam, Airtable Consultant - thetimesaving.company
S
M
K
Forum|alt.badge.img+3
  • KevinT
  • Author
  • New Participant
  • January 24, 2023

Could you try the following out?  I think it's down to the format that lookup fields display data in, so we convert it to a string and then into a number, and it seems to work?

DATEADD( {Submission Date}, VALUE(CONCATENATE({Health Plan SLA})), 'days' )

Thank you, Adam! The formula is working now, however, the date is still slightly off. It is returning a date that is one day ahead of the expected SLA. In the attached screenshot, the submission date is 9/19/22. With a 90-day SLA, it should return a date of 12/18/2222 but we're getting 12/17/22. 

1 Attachments
TheTimeSavingCo
Forum|alt.badge.img+31

Thank you, Adam! The formula is working now, however, the date is still slightly off. It is returning a date that is one day ahead of the expected SLA. In the attached screenshot, the submission date is 9/19/22. With a 90-day SLA, it should return a date of 12/18/2222 but we're getting 12/17/22. 


Hmm, could be a timezone issue; may I know what timezone your formula field's set to?

Here's some screenshots of my attempt at replicating what you're facing:

Adam, Airtable Consultant - thetimesaving.company
K
Forum|alt.badge.img+3
  • KevinT
  • Author
  • New Participant
  • January 25, 2023

Hmm, could be a timezone issue; may I know what timezone your formula field's set to?

Here's some screenshots of my attempt at replicating what you're facing:


The timezone would be GMT-5 (EST)

TheTimeSavingCo
Forum|alt.badge.img+31

The timezone would be GMT-5 (EST)


Yeah, once you chage the formula's timezone to match the original date field's timezone it should work as expected I believe

Adam, Airtable Consultant - thetimesaving.company
K
Forum|alt.badge.img+3
  • KevinT
  • Author
  • New Participant
  • January 29, 2023

Yeah, once you chage the formula's timezone to match the original date field's timezone it should work as expected I believe


Thank you! That fixed it. 

TheTimeSavingCo
Terms & ConditionsAccessibility statement