# Using Nested IF and DateADD formula

Topic Labels: Formulas
Solved
574 5
cancel
Showing results for
Did you mean:  5 - Automation Enthusiast

I’m try to create a new field (Event End Date) based on the frequency of the Event (Event Day) and when the Event Day Started (Event Start Date). I started using the formula below but it doesn’t work. In addition, I need to code for Event Days where it’s a single day (i.e., “Thursdays”, “Fridays”, etc.) in which case the Event End Date = Event Start Date.

``````IF(
FIND ( "Tue - Fri",  {Event Day}), DATEADD({Event Start Date},4,  ‘days’)),
IF(
FIND ( "Tue-Fri",  {Event Day}), DATEADD({Event Start Date},4,  ‘days’)),
IF(
FIND ( "Wed - Sat",  {Event Day}), DATEADD({Event Start Date},4,  ‘days’)),
IF(
FIND "Tue - Thur”, {Event Day}), DATEADD({Event Start Date},3 ,  ‘days’)),
IF(
FIND "Tue - Thu”, {Event Day}), DATEADD({Event Start Date},3 ,  ‘days’)),
IF(
FIND "Wed-Fr”, {Event Day}), DATEADD({Event Start Date},3 , ‘days’),
IF(
{Event Day} = "Thur - Fri", DATEADD({Event Start Date},2,  ‘days’)),
IF (
{Event Day} = "Tue - Sat", DATEADD({Event Start Date},5, ‘days’)
)
)
)
)
)
)
``````

)
)

1 Solution

Accepted Solutions  16 - Uranus

since the answer to ^ that question is apparently yes, the most efficient formula would be something like this:

``````IF(
AND({Event Day}, {Event Start Date}),
{Event Start Date},
SWITCH(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Event Day}, " ", ""), "days", ""), "day", ""),
"Tue-Sat", 5,
"Thur-Fri", 2,
"Wed-Fri", 3,
"Tue-Fri", 4,
"Mon", 0,
"Tue", 0,
"Tues", 0,
"Wed", 0,
"Thurs", 0,
"Fri", 0,
"Sat", 0,
"Sun", 0
),
"days"
)
)
``````

Or:

``````IF(
AND({Event Day}, {Event Start Date}),
{Event Start Date},
SWITCH(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Event Day}, " ", ""), "days", ""), "day", ""),
"Tue-Sat", 5,
"Thur-Fri", 2,
"Wed-Fri", 3,
"Tue-Fri", 4,
0
),
"days"
)
)
``````
5 Replies 5  16 - Uranus

By any chance, would “Tue - Fri” comprise the entirety of a possible field value? Or would “Tue - Fri” appear amongst other text in the same cell? Is it possible to clean up your values so `{Event Day}` is more predictable (so you don’t have to differentiate between “Tue - Thur” and “Tue - Thu”)?  5 - Automation Enthusiast

Figured it out. There’s probably a way more efficient way to do this, but here’s what I came up with:

IF({Event Day} = “Tue - Sat”, DATEADD({Event Start Date}, 5, ‘day’), IF({Event Day}= “Thur - Fri”, DATEADD({Event Start Date}, 2), IF({Event Day}= “Wed-Fri”, DATEADD({Event Start Date}, 3, ‘day’), IF({Event Day}= “Tue - Fri”, DATEADD({Event Start Date}, 4, ‘day’), IF({Event Day}= “Tuesdays”, {Event Start Date}, IF({Event Day}= “Tues”, {Event Start Date}, IF({Event Day}= “Wed”, {Event Start Date}, IF({Event Day}= “Thurs”, {Event Start Date}, IF({Event Day}= “Monday”, {Event Start Date}, IF({Event Day}= “Mondays”, {Event Start Date}, IF({Event Day}= “Tuesday”, {Event Start Date}, IF({Event Day}= “Wednesday”, {Event Start Date}, IF({Event Day}= “Thursday”, {Event Start Date}, IF({Event Day}= “Friday”, {Event Start Date}, IF({Event Day}= “Saturday”, {Event Start Date}, IF({Event Day}= “Thursdays”, {Event Start Date}, IF({Event Day}= “Fridays”, {Event Start Date}, IF({Event Day}= “Sunday”, {Event Start Date}, IF({Event Day}= “Tue”, {Event Start Date}, IF({Event Day}= “Saturdays”, {Event Start Date}))))))))))))))))))))  16 - Uranus

since the answer to ^ that question is apparently yes, the most efficient formula would be something like this:

``````IF(
AND({Event Day}, {Event Start Date}),
{Event Start Date},
SWITCH(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Event Day}, " ", ""), "days", ""), "day", ""),
"Tue-Sat", 5,
"Thur-Fri", 2,
"Wed-Fri", 3,
"Tue-Fri", 4,
"Mon", 0,
"Tue", 0,
"Tues", 0,
"Wed", 0,
"Thurs", 0,
"Fri", 0,
"Sat", 0,
"Sun", 0
),
"days"
)
)
``````

Or:

``````IF(
AND({Event Day}, {Event Start Date}),
{Event Start Date},
SWITCH(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Event Day}, " ", ""), "days", ""), "day", ""),
"Tue-Sat", 5,
"Thur-Fri", 2,
"Wed-Fri", 3,
"Tue-Fri", 4,
0
),
"days"
)
)
``````  5 - Automation Enthusiast

They both worked beautifully! Thank you so much :grinning:  18 - Pluto

You write beautiful formulas. 