Re: IF DATE IS AFTER X Multiply this by 0.10

981 0
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer


Costings within my business have now changed slightly and have added in a new column but I only want the records after 1st October to calculate the 10%.

Please help, thank you.

IF({Date Purchased}IS_AFTER(01/10/2020),SUM({Net Revenue}*0.1))

5 Replies 5
7 - App Architect
7 - App Architect

Hi Karen, Welcome to the forums!

I think this is what you’re looking for:
IF(IS_AFTER({Date Purchased}, '01/10/2020'), {Net Revenue}*.1)

That will display 10% of {Net Revenue}, only if the date is after 1/10/20. If you were trying to ADD 10% to {Net Revenue}, then this should work:

IF(IS_AFTER({Date Purchased}, '01/10/2020'), {Net Revenue} + {Net Revenue}*.1)

It seems to work best if the {Date Purchased} field is date only, not including time. Another thing to consider is that the formula formatting should probably be set to decimal, with whatever precision you want it rounding to.

Hope that helps!

4 - Data Explorer
4 - Data Explorer

Thank you!

So it worked but it also did everything before 01/10/2020

Screen Shot 2020-10-09 at 11.13.37 am

The first column is the 10% formula

I noticed that you have your dates formatted as Day/Month/Year.
However, since you do not specify the date format, Airtable probably thinks that you want dates after January 10, 2020, not dates after 1 October 2020.

You can tell Airtable the format of your date by using DATETIME_PARSE

IS_AFTER({Date Purchased}, DATETIME_PARSE('01/10/2020', 'DD/MM/YYYY'))

To clarify this point for @Karen_Coram and @Nick_Dennis , both of the values passed to the IS_AFTER() function must be datetime values. Writing out the date (with or without surrounding quotes) doesn’t create a true datetime value. You have to either reference a field that contains a datetime value (like a date field, or another formula field that properly creates a datetime value as a result) or do what @kuovonne did and use DATETIME_PARSE() to parse a string, which will return a datetime value.

7 - App Architect
7 - App Architect

Thanks for clarifying this @Justin_Barrett and @kuovonne. That makes a lot of sense.

So I think the final formula you are looking for, @Karen_Coram is:
IF(IS_AFTER({Date Purchased}, DATETIME_PARSE('01/10/2020', 'DD/MM/YYYY')), {Net Revenue}*.1)

And when entering the formula, you can select Formatting and choose Currency as the format.