If and is_after

nnnnneil · ‎Jul 30, 2019

Need a little help here :slightly_smiling_face:

What i’m after is: If code is ER and Order Received is after 29th July 2019, value is 21 else value is 17.5.

The order received field contains date and time like: 29 July 2019 11:01am

IF(AND({Code}='ER',IS_AFTER({Order Received},"29 July 2019 12:00am"),21,17.5))

Justin_Barrett · ‎Jul 30, 2019

You’re close. To process your date string as an actual date, you need to wrap it inside DATETIME_PARSE (details here).

IF(AND({Code}='ER',IS_AFTER({Order Received}, DATETIME_PARSE("29 July 2019 12:00am", "DD MMMM YYYY hh:mma")),21,17.5))

nnnnneil · ‎Jul 30, 2019

Oh nice @Justin_Barrett. That improved the situation - i’m not getting #ERROR anymore. But i’m also not getting either of the values on either side of the date which is interesting.

nnnnneil · ‎Jul 30, 2019

Ah i think it always outputs in US date format!

W_Vann_Hall · ‎Jul 30, 2019

In that case, wrap @Justin_Barrett’s code in a DATETIME_FORMAT() function and control precisely how you want the output by using the appropriate format specifier.

nnnnneil · ‎Jul 30, 2019

Thanks. So i’ve tried a couple of methods but it’s bringing back the #ERROR!

Here’s what I currently have:

IF(AND({Code}='ER',DATETIME_FORMAT(IS_AFTER({Order Received},DATETIME_PARSE("30 July 2019 12:00am","DD MMMM YYYY hh:mma")),"DD MMMM YYYY hh:mma"),21,17.5))

W_Vann_Hall · ‎Jul 30, 2019

Oops, sorry: I misunderstood your issue.

When you say,

do you mean the value you are storing in {Order Received}?

More importantly, what type of field is {Order Received}, date or single-line text?

(There is a missing closing parenthesis in the formula you provided — but the underlying formula is invalid, anyway, so fixing the syntax won’t help. I think your problem lies with {Order Received}; once you tell me what it is, I’ll see if I can help clear the issue.)

nnnnneil · ‎Jul 30, 2019

Hey @W_Vann_Hall,

The {Order Received} is a date field with time in the format of 31 July 2019 10:43am. The date i’m comparing is hard coded as a string so @Justin_Barrett suggested that needed to be converted using DATETIME_PARSE which unfortunately assumes a US date format. So now I think I have the hard coded date converted but not matching due to the different date formats.

Thanks for your help!

W_Vann_Hall · ‎Jul 31, 2019

Well, the point of DATETIME_PARSE() is to convert a text date string into Airtable’s internal date format, so there should be no possibility of {Order_Received} not matching your hardcoded date field. I suspect there may be an error in your formula syntax, which is why you aren’t seeing either 21 or 17.5 as output.

You might also receive an #ERROR! message in cases where there isn’t an {Order_Received} value. I think this version of the formula should fix both problems — but I’m not sure AND() short-circuits at the first failed test. If you get an error when {Order_Received} is blank, let me know, and I’ll give you version #2 of this routine.

Of course, it if doesn’t work at all, let me know how it fails, and we’ll keep going. :winking_face:

IF(
    AND(
        {Order Received},
        {Code}='ER',
        IS_AFTER(
            {Order Received},
            DATETIME_PARSE(
                "30 July 2019 12:00am",
                "DD MMMM YYYY hh:mma"
                )
            )
        ),
    21,
    17.5
    )

nnnnneil · ‎Jul 31, 2019

Hey @W_Vann_Hall,
That works perfectly. Looks like testing if {Order Received} exists was required?
Thanks heaps!