
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 21, 2021 11:10 AM
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!!
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 21, 2021 12:13 PM
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')
)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 21, 2021 12:13 PM
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')
)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 21, 2021 12:43 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 21, 2021 12:47 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 21, 2021 12:48 PM
You can use the “Formatting” tab in the field options for this formula field to tell it to only display the date.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 21, 2021 12:53 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 21, 2021 01:05 PM
@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:
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 12, 2023 07:00 AM
@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)?
