- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 23, 2023 01:49 PM
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.
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 23, 2023 09:10 PM
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'
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 23, 2023 09:10 PM
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'
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 24, 2023 10:34 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 24, 2023 10:48 PM
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:
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 25, 2023 08:28 AM
The timezone would be GMT-5 (EST)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 25, 2023 09:18 PM
Yeah, once you chage the formula's timezone to match the original date field's timezone it should work as expected I believe
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 29, 2023 11:39 AM
Thank you! That fixed it.
