Skip to main content
Solved

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

  • August 21, 2020
  • 3 replies
  • 45 views
Avi_Doe
Forum|alt.badge.img+11

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

Best answer by JonathanBowen

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.

    3 replies

    JonathanBowen
    Forum|alt.badge.img+18
    • JonathanBowen
    • Inspiring
    • Answer
    • August 21, 2020

    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
    Avi_Doe
    Forum|alt.badge.img+11
    • Avi_Doe
    • Author
    • Known Participant
    • August 21, 2020

    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)

    Justin_Barrett
    Forum|alt.badge.img+21

    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)
    JonathanBowen
    Avi_Doe
    Terms & ConditionsAccessibility statement