Need help with a function that will show whether time off conflicts with events.

Options

Hello,

I have 2 sheets. The first is to record time off requests:

The second sheet is for scheduling events:

Now, if an employee has requested time off on the Time Off sheet, and a date during that request coincides with a date and time on the Event sheet, I would like it to list "OOO" in that employee's column.

I have been working on an IF(COUNTIFS()) formula that I can apply to each column but I can't seem to get it 100% right. We use military time (ex. 0600, 1300, 2400), which seems to be causing some issues when I try to use => or =<.

Here is a part of my formula in progress for reference: =IF(COUNTIFS({Name}, [Employee 1]1, {Start Date}, <=Date@row, {End Date}, >=Date@row, {Start Time}, >=[Event Start Time]@row, {End Time}, >=[Event End Time]@row) > 0, "OOO", "Available")

Does anyone have any advice/solution on this?

Thank you!

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Max Essig

    Since the {Start Time} and [Event Start Time]@row are text values, as "11:30", you can not use ">=" to compare them.😀

    So, I suggest converting them to some values, such as Start Time Minutes.

    Example Formula for Start Time Minute:

    =VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) * 60 + VALUE(RIGHT([Start Time]@row, 2))

    Then, you can compare the converted values with ">=," etc.

    The logic to determine whether an employee's time off conflicts with the event schedule turned out to be a little complicated, and I came up with this formula;

    Example Formula for Employee 1:

    =IF(COUNTIFS({Name}, "Employee 1", {Start Minute}, <=[Event End Minute]@row) > 0, 1, 0) * IF(COUNTIFS({Name}, "Employee 1", {End Minute}, >=[Event Start Minute]@row) > 0, 1, 0)

    ( I prefer the column formula, so I use "Employee 1" instead of [Employee 1]$1.)



Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Max Essig

    Since the {Start Time} and [Event Start Time]@row are text values, as "11:30", you can not use ">=" to compare them.😀

    So, I suggest converting them to some values, such as Start Time Minutes.

    Example Formula for Start Time Minute:

    =VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) * 60 + VALUE(RIGHT([Start Time]@row, 2))

    Then, you can compare the converted values with ">=," etc.

    The logic to determine whether an employee's time off conflicts with the event schedule turned out to be a little complicated, and I came up with this formula;

    Example Formula for Employee 1:

    =IF(COUNTIFS({Name}, "Employee 1", {Start Minute}, <=[Event End Minute]@row) > 0, 1, 0) * IF(COUNTIFS({Name}, "Employee 1", {End Minute}, >=[Event Start Minute]@row) > 0, 1, 0)

    ( I prefer the column formula, so I use "Employee 1" instead of [Employee 1]$1.)



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!