Help

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

Solved
Jump to Solution
1782 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Avi_Doe
6 - Interface Innovator
6 - Interface Innovator

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

1 Solution

Accepted Solutions
JonathanBowen
13 - Mars
13 - Mars

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.

See Solution in Thread

3 Replies 3
JonathanBowen
13 - Mars
13 - Mars

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.

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)

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)