Solved

Using Nested IF and DateADD formula

Forum|Forum|4 years ago
June 29, 2021
5 replies
45 views

Automagical
New Participant

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

)
)

Best answer by Kamille_Parks11

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

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}),
   DATEADD(
      {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}),
   DATEADD(
      {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"
   )
)

+27

Kamille_Parks11
Brainy
Forum|Forum|4 years ago
June 29, 2021

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”)?

Automagical
Author
New Participant
Forum|Forum|4 years ago
June 30, 2021

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

+27

Kamille_Parks11
Brainy
Answer
Forum|Forum|4 years ago
June 30, 2021

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

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}),
   DATEADD(
      {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}),
   DATEADD(
      {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"
   )
)

Automagical
Author
New Participant
Forum|Forum|4 years ago
June 30, 2021

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}),
   DATEADD(
      {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}),
   DATEADD(
      {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"
   )
)

They both worked beautifully! Thank you so much :grinning:

+29

kuovonne
Brainy
Forum|Forum|4 years ago
June 30, 2021

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}),
   DATEADD(
      {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}),
   DATEADD(
      {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"
   )
)

You write beautiful formulas.

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded