Help

Date Calculation Formula

Topic Labels: Formulas
Solved
Jump to Solution
3154 6
cancel
Showing results for 
Search instead for 
Did you mean: 
KevinT
5 - Automation Enthusiast
5 - Automation Enthusiast

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. 

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

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'
)

See Solution in Thread

6 Replies 6
TheTimeSavingCo
18 - Pluto
18 - Pluto

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. 

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:

Screenshot 2023-01-25 at 2.47.08 PM.png

Screenshot 2023-01-25 at 2.46.55 PM.png

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

Thank you! That fixed it.