Skip to main content

Hi everyone,


I am trying to use a formula that works for me in GoogleSheets, and convert it to be AirTable friendly. 🙂


IF({Event Type}=“Pickup”, (IF(TODAY()-{Event Date}<=0,“P/U”, IF(TODAY()>={Return Date},“RET”, “ABS”))),{Event Type})


The gist:

I have a pickup date field {Event Date}, and a return date field {Return Date}, and would like to display the following: If today is the same as the pickup date, display “P/U,” if today is the return date, display “Ret,” if it’s any date in between those two, display “ABS” (for absent). This giant formula should only be applied if the event type is “Pickup,” and if it isn’t, I just want the Event Type displayed (e.g. Out Sick, On Vacation, etc). It is recognizing my IF({Event Type}=“Pickup” ok, but it’s breaking when calculating the middle. Any suggestions would be GREATLY appreciated! I am at a loss. 🙂


Thanks!

Don’t worry Susan, I got you! I’ve chosen to compare the dates directly to simplify it a bit, rather than subtracting one from the other, but your way could work just as well. You may have just run into a syntax error.


Try this out and let me know if it works for you.


IF(

    {Event Type}="Pickup",

     IF(

        {Event Date}=Today(),

        "P/U",

        IF(

            {Return Date}=Today(),

            "Ret",

            "ABS"

     ),

    {Event Type}

)

-Danny

Don’t worry Susan, I got you! I’ve chosen to compare the dates directly to simplify it a bit, rather than subtracting one from the other, but your way could work just as well. You may have just run into a syntax error.


Try this out and let me know if it works for you.


IF(

    {Event Type}="Pickup",

     IF(

        {Event Date}=Today(),

        "P/U",

        IF(

            {Return Date}=Today(),

            "Ret",

            "ABS"

     ),

    {Event Type}

)

-Danny


Hi Danny,


Thanks so much for the speedy reply! 🙂 Your formula works beautifully, THANK YOU!


I have one more question for you though… when I select “include a time field” in the date column, the results always shows “ABS,” I am assuming because it is now trying to match exact date AND time. (When I turn it back off, it calculates correctly showing P/U and RET where appropriate.) Any ideas on how to add the ability to have a time as well, but have it only look for the date info to match? I’m unclear as to how to add the date formatting info into a formula. I am hoping it’s possible!


Thanks so much for your help!


-Susan

You could do it this way:


IF(

    {Event Type}="Pickup",

    IF(

        DATETIME_DIFF({Event Date}, Today(), 'days')=0,

        "P/U",

        IF(

            DATETIME_DIFF({Return Date}, Today(), 'days')=0,

            "Ret",

            "ABS"

        ),

    {Event Type}

)

Basically, the DATETIME_DIFF calculates the difference between two dates according to the units you specify (in our case, this is ‘days’, so it’ll ignore the time component when calculating the difference).

You could do it this way:


IF(

    {Event Type}="Pickup",

    IF(

        DATETIME_DIFF({Event Date}, Today(), 'days')=0,

        "P/U",

        IF(

            DATETIME_DIFF({Return Date}, Today(), 'days')=0,

            "Ret",

            "ABS"

        ),

    {Event Type}

)

Basically, the DATETIME_DIFF calculates the difference between two dates according to the units you specify (in our case, this is ‘days’, so it’ll ignore the time component when calculating the difference).


Perfect! I would not have thought to use DATETIME_DIFF. Thanks for the suggestion.


I added a > to the event date portion of the formula so that it would only show ABS between the P/U and RET dates, but other than that, your formula did exactly what I needed & works great!


Thanks for working your magic! You kept me from tearing my hair out over here. 🙂

Reply


Terms & Conditions