Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Due Date Based on Single Select Field?

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

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

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

6 Replies 6

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: