Need formula to indicate double booking on the same date.

Brandon Walker

I'm looking for a formula to highlight double-booked personnel on the same day.

Carson Penticuff

The color of the range in the calendar is actually defined by the "task bar" setting under the conditional format. When I set a background color, the task bar color is automatically set as well unless I override it with something else. You may double check to make sure this is set to red as well. Maybe this behavior is somewhat browser dependent, but it behaves the same for me on Edge and Firefox under Windows.

Carson Penticuff

This depends somewhat on how your sheet would be setup, but here is the basic idea:

Add a helper column (checkboxes work well) and add the following as a column formula.

=IF(COUNTIFS([Date]:[Date], @cell = [Date]@row, [Assigned Personnel]:[Assigned Personnel], @cell = [Assigned Personnel]@row) > 1, 1, 0)

Then you can add conditional formatting, as pictured above, to highlight either the specific column(s) or entire row depending how you want the result to be displayed. Additionally, you can hide the helper column after it is setup and it will continue to function, if you do not want it to be visible.

Brandon Walker

If a person is assigned to two projects simultaneously, I would like for the affiliated rows to be highlighted in red.

Carson Penticuff

This is more complex, especially since we are not just looking for duplicate dates, but also for overlapping date ranges. This should work, but will still require a helper column to identify the duplicates.

=IF(AND(NOT(ISBLANK([Task Name]@row)), COUNT(CHILDREN()) = 0), IF(OR(COUNTIFS([Task Name]:[Task Name], [Task Name]@row, [Start Date]:[Start Date], >= [Start Date]@row, [Start Date]:[Start Date], <= [End Date]@row) > 1, COUNTIFS([Task Name]:[Task Name], [Task Name]@row, [End Date]:[End Date], >= [Start Date]@row, [End Date]:[End Date], <= [End Date]@row) > 1), 1, 0))

A very scaled down version of your sheet using the above formula:

Brandon Walker

Thank you for your assistance! This is exactly what I needed. I have another question: how can I display double-bookings in the "Calendar View"?

Carson Penticuff

The conditional formatting should carry over to the calendar view as well, making the double bookings visible there also.

Brandon Walker

I may have something enabled or disabled that's preventing conditional formatting transfer. Can you advise?

Carson Penticuff

I am unaware of a setting that would cause that. Do you have the conditional formatting set to "entire row"?

Brandon Walker

Yes, the entire row works on grid view, but it doesn't transfer to the calendar. Not sure what to do at this point.

Carson Penticuff

The color of the range in the calendar is actually defined by the "task bar" setting under the conditional format. When I set a background color, the task bar color is automatically set as well unless I override it with something else. You may double check to make sure this is set to red as well. Maybe this behavior is somewhat browser dependent, but it behaves the same for me on Edge and Firefox under Windows.

Brandon Walker

That WORKED!!!! Thank you so much. You just made life a WHOLE LOT EASIER!!!!!

Carson Penticuff

Awesome, I glad that fixed it! 👍

Brandon Walker

Carson, O mentor! I want to thank you for your help thus far. I really appreciate it!

I have a question about displaying personnel availability. Is there a specific table, graph, or grid that I can use? If so, could you please provide guidance on how to set it up?

Brandon Walker

I assume you don't work for Smartsheet. If not, sorry for the bother. This is my first time on the Smartsheet Community Blog. Thank you for your assistance.

Quick Question: Do you know of any formula that could help identify when our team members are available? Sometimes, it's hard to tell who's free to work on an available new project. Any ideas? If not, could you point me in the right direction?

Carson Penticuff

@Brandon Walker You are correct in that I do not work for Smartsheet, I am just a regular user.

As far as your team member availability conundrum, that could be a tough one. Smartsheet does offer a Resource Management add-on that would do exactly what you are asking, but I have not personally used it. You would have to request a quote and determine whether the cost would justify its usefulness in your specific case.

If I were setting something like this up for myself, I believe I would create a new sheet (grid) and populate it with all team members and their specific trade/job role. You could then add a new columns in your working project list for "trade" and "available team members". When setting up your project, you would enter the dates and "trade" into the new row, and you could use a formula to pull all team members from your new sheet that match that trade, and return a list of all of the ones that do not have an assignment during that time period. If you wanted to take into account what team members may be available for a portion of the job, things get much more difficult. If you were to implement this, you would have to be sure to keep your new sheet up-to-date with current team members and their roles.

Brandon Walker

Carson,

Thanks for your help. I linked another sheet and alphabetized personnel to create a Gantt chart showing availability. I appreciate all the help.

We are ramping up around here, and I'm just trying to keep the train on the tracks.

Thanks again,

BW