Due Date Based on Single Select Field?

[Sara_Stahlberg]

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!!

[Jeremy_Oglesby · Accepted solution]

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

[Sara_Stahlberg]

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.

[Jeremy_Oglesby]

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.

[Jeremy_Oglesby]

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

[Sara_Stahlberg]

Jeremy_Oglesby:

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

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.

[Sara_Stahlberg]

@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]

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