Need help with a function that will show whether time off conflicts with events.
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!