Commission Calculation Formula

[KevinT]

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. 

[TheTimeSavingCo · Accepted solution]

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

[KevinT]

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. 

[TheTimeSavingCo]

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:

[KevinT]

The timezone would be GMT-5 (EST)

[TheTimeSavingCo]

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

[KevinT]

Thank you! That fixed it.