Check to see if a Date is missing and make code more efficient

Mark_Sanborn · ‎Feb 14, 2022

My question has 2 parts to it.
I need to check for a missing date in 8 columns (4 sets of Start and End Date) that has a start date or end date, but not the other. I use these columns to calculate vacation days used. So if someone inputs a start date but forgets to put a end date in, I want to show “Start or End Date Missing” instead of the calculation.
This works so far for one start/end column pair.

IF(LEN({Vacation Start 1})>0, IF(LEN({Vacation End 1})=0),“Missing a Start or End Date”, BLANK())

But I am looking to group all 8 columns to one answer like this

IF(LEN({Vacation Start 1})>0, IF(LEN({Vacation End 1})=0,“Missing a Start or End Date”, BLANK()),
IF(LEN({Vacation Start 2})>0, IF(LEN({Vacation End 2})=0,“Missing a Start or End Date”, BLANK()),
IF(LEN({Vacation Start 3})>0, IF(LEN({Vacation End 3})=0,“Missing a Start or End Date”, BLANK()),
IF(LEN({Vacation Start 4})>0, IF(LEN({Vacation End 4})=0,“Missing a Start or End Date”, BLANK()),BLANK()))))

But this just looks at the first IF statement (Vacation Start/End 1). I am guessing that there is a more efficient way to write this too.

I am also looking to see if there is a better way to Sum date columns and in the end, add the date check code above into the same equation.

This SUM code works well for me now, but doesn’t check to see if someone forgot to complete both start and end date

SUM(IF(AND({Vacation Start 1},{Vacation End 1}), DATETIME_DIFF({Vacation End 1},{Vacation Start 1},‘d’),0),
IF(AND({Vacation Start 2},{Vacation End 2}), DATETIME_DIFF({Vacation End 2},{Vacation Start 2},‘d’),0),
IF(AND({Vacation Start 3},{Vacation End 3}), DATETIME_DIFF({Vacation End 3},{Vacation Start 3},‘d’),0),
IF(AND({Vacation Start 4},{Vacation End 4}), DATETIME_DIFF({Vacation End 4},{Vacation Start 4},‘d’),0))

In the end I would like to combine all of this into one.

Thanks in advance

Kamille_Parks · ‎Feb 14, 2022

You’re not nesting IFs properly. IF() statements take 3 arguments and your first formula has 4. In your first formula, you put IF(LEN({Vacation End 1})=0) in the spot where the “value if true” bit of a formula should go. The proper way to write just that portion would be:

IF(
   OR(LEN({Vacation Start 1})=0, IF(LEN({Vacation End 1})=0)),
   "Missing a Start or End Date", 
   BLANK()
)

^ but that could also be rewritten simpler. You don’t need to check the length of a field to see if its empty.

IF(
   OR({Vacation Start 1}, {Vacation End 1}), 
   BLANK(),
   "Missing a Start or End Date"
)

Since it doesn’t appear to matter which start/end is missing or how many, you could do this all in one IF(OR()) statement

IF(
   OR({Vacation Start 1},{Vacation End 1},{Vacation Start 2},{Vacation End 2},{Vacation Start 3},{Vacation End 3},{Vacation Start 4},{Vacation End 4}), 
   BLANK(),
   "Missing a Start or End Date"
)

That does not seem advisable. You described one formula that outputs a text string, ("Missing a Start or End Date") and another that outputs a number. Airtable does not do mixed-format columns. Meaning if any possible outcome of a formula is a string, all outcomes will be forced into string format. I assume you want numbers to be actual numbers.

If you don’t want to perform the SUM unless all dates have been filled in, then restructure your last formula as:

IF(
   AND({Vacation Start 1},{Vacation End 1},{Vacation Start 2},{Vacation End 2},{Vacation Start 3},{Vacation End 3},{Vacation Start 4},{Vacation End 4}),
   SUM(
      DATETIME_DIFF({Vacation End 1},{Vacation Start 1},'d'),
      DATETIME_DIFF({Vacation End 2},{Vacation Start 2},'d'),
      DATETIME_DIFF({Vacation End 3},{Vacation Start 3},'d'),
      DATETIME_DIFF({Vacation End 4},{Vacation Start 4},'d')
   )
)

^ The above will output a blank if one or more of the 8 date fields is empty.

Ben_Young1 · ‎Feb 14, 2022

Hey @Mark_Sanborn! Welcome to the forums!

Truthfully, there is probably a more efficient way to write this, but I just kinda winged it and cleaned it up a bit.

Here’s my version of your formula:

IF(
    AND(
        {Vacation Start 1},
        {Vacation Start 2},
        {Vacation Start 3},
        {Vacation Start 4},
        {Vacation End 1},
        {Vacation End 2},
        {Vacation End 3},
        {Vacation End 4}
    ),
    ABS(
        SUM(
            IF(
                AND(
                    {Vacation Start 1},
                    {Vacation End 1}
                ),
                DATETIME_DIFF(
                    {Vacation Start 1},
                    {Vacation End 1},
                    "days"
                )
            ) +
            IF(
                AND(
                    {Vacation Start 2},
                    {Vacation End 2}
                ),
                DATETIME_DIFF(
                    {Vacation Start 2},
                    {Vacation End 2},
                    "days"
                )
            ) +
            IF(
                AND(
                    {Vacation Start 3},
                    {Vacation End 3}
                ),
                DATETIME_DIFF(
                    {Vacation Start 3},
                    {Vacation End 3},
                    "days"
                )
            ) +
            IF(
                AND(
                    {Vacation Start 4},
                    {Vacation End 4}
                ),
                DATETIME_DIFF(
                    {Vacation Start 4},
                    {Vacation End 4},
                    "days"
                )
            )
        )
    ) &
    " Days Total",
    IF(
        AND(
            NOT(
                AND(
                    {Vacation Start 1},
                    {Vacation Start 2},
                    {Vacation Start 3},
                    {Vacation Start 4}
                )
            ),
            NOT(
                AND(
                    {Vacation End 1},
                    {Vacation End 2},
                    {Vacation End 3},
                    {Vacation End 4}
                )
            )
        ),
        "Missing Start & End Date Information",
        IF(
            AND(
                {Vacation Start 1},
                {Vacation Start 2},
                {Vacation Start 3},
                {Vacation Start 4},
                NOT(
                    AND(
                        {Vacation End 1},
                        {Vacation End 2},
                        {Vacation End 3},
                        {Vacation End 4}
                    )
                )
            ),
            "Missing End Date Data!"
        ) &
        IF(
            AND(
                {Vacation End 1},
                {Vacation End 2},
                {Vacation End 3},
                {Vacation End 4},
                NOT(
                    AND(
                        {Vacation Start 1},
                        {Vacation Start 2},
                        {Vacation Start 3},
                        {Vacation Start 4}
                    )
                )
            ),
            "Missing Start Date Data!"
        )
    )
)

Here’s the final product:

When Filled In Correctly

Missing All Date Information (Both Start & End Dates)

Missing End Date Data

To clarify past the screenshots, the formula doesn’t need to have all of the end or start date fields be blank for the messages to appear.
If all the fields are completed except for one end date, then the end date message will appear.
The same behavior is true for the start dates.

If you want to modify it a bit more, lemme know and I’ll walk you through how you can change things around to modify how it appears or logs information.

Mark_Sanborn · ‎Feb 14, 2022

Thank you both for the ideas. I should have mentioned that not all would be filled in, only if one of the pair of dates (vacation 1, vacation 2, etc) were missing a start or end. But with both your responses I got some ideas that worked for now, which gives me time to polish it up a bit later.

This is what I ended up with that works for what I was looking for. (sorry for the harder to read format)

IF(AND({Vacation Start 1},NOT({Vacation End 1})),“Vacation 1 Missing End Date”,IF(AND({Vacation End 1},NOT({Vacation Start 1})),“Vacation 1 Missing Start Date”,
IF(AND({Vacation Start 2},NOT({Vacation End 2})),“Vacation 2 Missing End Date”,IF(AND({Vacation End 2},NOT({Vacation Start 2})),“Vacation 2 Missing Start Date”,
IF(AND({Vacation Start 3},NOT({Vacation End 3})),“Vacation 3 Missing End Date”,IF(AND({Vacation End 3},NOT({Vacation Start 3})),“Vacation 3 Missing Start Date”,
IF(AND({Vacation Start 4},NOT({Vacation End 4})),“Vacation 4 Missing End Date”,IF(AND({Vacation End 4},NOT({Vacation Start 4})),“Vacation 4 Missing Start Date”))))))))

Ben, I wasn’t able to get your code to work (formatting issues), but am intrigued on the approach. I am new to Airtable, but not new to Excel, so I will paly with the code to learn.

Thanks