Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Using Nested IF and DateADD formula

Topic Labels: Formulas
Solved
Jump to Solution
432 5
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

See Solution in Thread

5 Replies 5

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

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

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

You write beautiful formulas.