Help

Due Date Based on Single Select Field?

Topic Labels: Formulas
Solved
Jump to Solution
1423 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Sara_Stahlberg
6 - Interface Innovator
6 - Interface Innovator

Hi all! I am building out something to help with vaccine planning (yay!) and need something that can essentially help me do the following:

  • I have a single select field called “Vaccine” with Moderna (First Dose), Pfizer (First Dose), Moderna (Second Dose), Pfizer (Second Dose), and J&J as options
  • I also have a date field (“Clinic Date”) indicating when the vaccine clinic takes place for each of them, and how many doses were administered.
  • What I am looking to do is to automatically calculate a field for the second dose clinic (i.e. it would need to identify the type of vaccine and calculate the next date based on that. For example, First Dose Pfizer - calculate for me the next date as 3 weeks out. For Moderna, the next clinic needs to be 4 weeks out.

I should add that I did try creating the following formula, but Airtable does not seem to like it:

SWITCH(
{Vaccine},
‘Pfizer (First Dose)’, DATEADD (3, ‘week’),
‘Moderna (First Dose)’, DATEADD(4, ‘week’))

I know that this should be simple, but I cannot seem to get it to work. If anybody has suggestions or can write this formula for me, I would be INCREDIBLY grateful!!

1 Solution

Accepted Solutions
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

Hi @Sara_Stahlberg

You basically had it right, you’re just missing a parameter in your DATEADD functions. You need to tell the DATEADD function what date to add the 3 or 4 weeks to!

SWITCH(
   {Vaccine},
   'Pfizer (First Dose)', DATEADD({Clinic Date}, 3, 'week'),
   'Moderna (First Dose)', DATEADD({Clinic Date}, 4, 'week')
)

See Solution in Thread

7 Replies 7
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

Hi @Sara_Stahlberg

You basically had it right, you’re just missing a parameter in your DATEADD functions. You need to tell the DATEADD function what date to add the 3 or 4 weeks to!

SWITCH(
   {Vaccine},
   'Pfizer (First Dose)', DATEADD({Clinic Date}, 3, 'week'),
   'Moderna (First Dose)', DATEADD({Clinic Date}, 4, 'week')
)

Thank you @Jeremy_Oglesby ! Looks like this is working for Pfizer but not Moderna for some reason.

It is also populating the date with a time field. I know that I can use the DATETIME FORMAT function here, but do you have a recommendation on where to plug that into the thread? I really only need the date.

Nope, there was just an erroneous whitespace between the first DATEADD and its opening paren. I fixed it in my code block above, so you can try copy-pasting again.

You can use the “Formatting” tab in the field options for this formula field to tell it to only display the date.

THANK YOU! It is definitely accepting this formula now, but only populating for Pfizer and not Moderna. Sorry if I’m being dense on this :slightly_smiling_face: I even triple checked that I’m spelling Moderna correctly here and in the single-select field.

@Jeremy_Oglesby - I fussed with this some more and magically it works! I cannot even tell you what changed other than me pasting in the formula a few more times lol

THANK YOU again for all of your help today! You are a true lifesaver as I figure this all out. :raised_hands:

airballer86
6 - Interface Innovator
6 - Interface Innovator

@Jeremy_Oglesby Is it possible to SWITCH based on 2 fields? I have a campaign work back schedule. I need to calculate 4 date fields (using DATEADD) from {Launch Date} based on 2 qualifier fields Send Size (large or small) and Channel (email or push)?