Skip to main content

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

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

)

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.

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.

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.


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

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 🙂 I even triple checked that I’m spelling Moderna correctly here and in the single-select field.


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 🙂 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. 🙌

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

Reply


Terms & Conditions