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

Max Essig

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!

jmyzk_cloudsmart_jp

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.)

https://app.smartsheet.com/b/publish?EQBCT=a8c2205ad79f4dbd999c3b386783897d

jmyzk_cloudsmart_jp

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.)

https://app.smartsheet.com/b/publish?EQBCT=a8c2205ad79f4dbd999c3b386783897d