If Formula, if between 2 dates

[Neil7]

Hi All,

Struggling with a formula if anyone can help!

I would like the fee to show in the field, if the date booked is a 2018 date. Then I’ll have another field pulling out all the 2019 fees etc. Then use a roll up to bring in the totals to another tab.

When I do the formula below it pulls in all Fees no matter what the date is, and when I click back in to the formula it has removed the ,0 part.

IF({Date Booked}>=(01/01/2018),{Date Booked}<=(31/12/2018),{Fee £},0)

Any help much appreciated!

Thanks
Neil

[W_Vann_Hall]

IF() functions take the form

IF(
    This_Is_True,
    Then_Do_This,
    Otherwise_Do_This
    )

Your statement [if the date comparison was phrased correctly] would give you a value of 1 for any {Date Booked} on or after January 1, 2018 and on or prior to December 31, 2018, and a value of {Fee £} for all other dates.

What you want is (and there are several ways you could phrase this)

IF(
    AND(
        IS_AFTER(
            {Date Booked},
            '12/31/2017'
            ),
        IS_BEFORE(
            {Date Booked},
            '1/1/2019'
            )
        ),
    {Fee £},
    0
    )

[Neil7]

Hi,

Thank you for your help, much appreciated! Small bit more help needed please if you have a moment?!

I have used the formula:

IF(AND(IS_AFTER({Date Completed},‘12/31/2017’ ),IS_BEFORE({Date Completed},‘1/1/2019’)),{Fee £},0)

It works except for the ‘otherwise do this’ - any dates completed outside of 2018 are giving the 0 which is what I want, but for items not yet completed/have no date, I am getting an #Error rather than a 0. Is there anyway to make it a 0 if no date is entered, as we only enter a date when a project is complete.

Also my dates are in English format, so 31/12/2017, however the formula defaults to American style dates. Could this become a problem at all/miss off any items?

Thanks
Neil

[Neil7]

Sorted using:

IF( {date completed}, ORIGINAL FORMULA )

Thanks for your help!

[W_Vann_Hall]

Glad you figured out the issue!

For future reference concerning European vs American date formats, evidently Airtable (well, actually, your browser) somehow divines your preferred format via some sort of deep hoodoo with your OS that seemingly works almost all of the time, except when it doesn’t. There have been occasional posts looking for the secret ganglion one can tickle to correct a geographically/culturally confused system — but if anyone has found it, the info hasn’t trickled down to me.

That said, should you ever find yourself in a situation where Airtable thanks 4 January is April Fool’s Day, or vice versa, there is a a simple fix. (Actually, it’s probably the way the formula was intended to be written, before someone discovered Airtable will do its best to figure out what is, strictly speaking, invalid code.)

IF(
    {Date Booked},
    IF(
        AND(
            IS_AFTER(
                {Date Booked},
                DATETIME_PARSE(
                    '31/12/2017',
                    'DD/MM/YYYY'
                    )
                ),
            IS_BEFORE(
                {Date Booked},
                DATETIME_PARSE(
                    '1/1/2019',
                    'DD/MM/YYYY'
                    )
                )
            ),
        {Fee £},
        0
        )
    )

Using DATETIME_PARSE() allows you to spell out the desired date format, should there ever be confusion. (Oh, this version of the formula includes your fix for the #ERROR! problem.)

[David_Craig]

Thanks for the answer. I note that Airtable uses US format dates, MM/DD/YYY, is there any way to change that to DD/MM/YYYY for those of us who don’t live in the states or is this a standard coding convention?