IF Formula for different sales tax rate dependent on date of performance

[Avi_Doe]

Hello Airtable community!

due to corona pandemic in Germany the sales tax was decreased from 19% to 16 % during the period from 01.07.2020 to 31.12.2020 (DD/MM/YYYY).

I have a column which shows the date of performance which is decisive for the tax rate. My formula is accepted by AT but it just seem not to calculate because the result is always 19%.

My formula is: IF(AND({Date of Perfomance}>06/30/2020,{Date of Perfomance}<01/01/2021),16,19)

Can you find the mistake I made?

Thank you for your help in advance!

Avi

[JonathanBowen · Accepted solution]

Hi @Avi_Doe - the Airtable formula functions need to be a bit different for this to work. To compare dates you need to use the IS_BEFORE and IS_AFTER methods. When you use these you need to parse the date string you are passing in to compare. So this in your formula:

{Date of Perfomance}>06/30/2020

becomes this:

IS_AFTER({Date of Perfomance}, DATETIME_PARSE('06/30/2020', 'MM/DD/YYYY'))

And similarly for the second date using IS_BEFORE.

[Avi_Doe]

Thank you very much! It worked well. :blush:

The complete formula I used was:

IF(AND(IS_AFTER({Date of performance}, DATETIME_PARSE(‘06/30/2020’, ‘MM/DD/YYYY’)),IS_BEFORE({Date of performance}, DATETIME_PARSE(‘01/01/2021’, ‘MM/DD/YYYY’))),16,19)

[Justin_Barrett]

FWIW, you can use > and < to compare dates. However, as @JonathanBowen pointed out, they need to be actual dates (parsed using DATETIME_PARSE(), or from a date field). This should also work:

IF(AND({Date of performance} > DATETIME_PARSE('06/30/2020', 'MM/DD/YYYY'), {Date of performance} < DATETIME_PARSE('01/01/2021', 'MM/DD/YYYY')),16,19)