Have different outputs based on which date range a date falls into

Today’s Workflows & Workarounds article is in response to a question we received here in the Zapier community:

The Challenge

This user wanted to output a specific word based on which date range a value fell into. So, for example, today is 5/11/2022. Using this table, we’d want to output the word “alpha”.

The Solution

We don’t have a way to do this with a Lookup table, but we can use a spreadsheet-style formula in our Formatter by Zapier app to construct an IF/AND statement that can handle this.

The Zap

Trigger: Schedule by Zapier (the user suggested this as a trigger)
Action: Formatter by Zapier — format the date into Unix time
Action: Formatter by Zapier — spreadsheet-style formula
Action: Whatever else you want/need to add

Trigger

Schedule by Zapier: daily, weekly, etc

This could be different based on your own workflow, but the user mentioned this and for simplicity’s sake we’ll use this.

Action #1

Formatter by Zapier: convert the date into Unix.

Converting the date into Unix makes it easier to use the spreadsheet-style formula because you can check whether a number like 1652307561 (that’s “right now” when I checked) is bigger than 1652140800 (5/10/2022) and smaller than 1653264000 (5/23/2022).

Action #2

Formatter by Zapier: spreadsheet-style formula (IF/AND)

This is where the magic happens. It’s a nested IF/AND formula. IF the date is greater than or equal to [lower bound for alpha] AND less than or equal to [upper bound for alpha] then output “alpha”, otherwise continue through the rest of the pieces to see which one it fits into (beta, charlie, delta, post-delta).

Copy the Formula

You would replace the “value” with a mapped field (in all likelihood) and change the lower and upper bounds for your Unix dates.

=IF(AND(value>=lower-bound-alpha,value<=upper-bound-alpha),"Alpha", IF(AND(value>=lower-bound-beta,value<=uper-bound-beta),"Beta", IF(AND(value>=lower-bound-charlie,value<=upper-bound-charlie),"Charlie", IF(AND(value>=lower-bound-delta,value<=upper-bound-delta),"Delta", "Post Delta"))))

Here’s a Shared Zap

If you want to get a head start on this workflow, and play around with it to see how it works, check out this Shared Zap:

https://zapier.com/shared/f74a6d17c9622ae03f70c67d1034a7133ddfad20

Wrapping Up

There are always multiple ways to solve workflow challenges, and as much as I do love to use external tools (like Airtable), sometimes it’s nice to do everything within the Zap itself :)