Forum Discussion
Trying to create a scheduling graph
Hello,
I am trying to create a scheduling graph. So, the schedule is made on one sheet, and that sheet is titled Schedule. On a separate sheet I have my graph. Let's say that boxes G5 and below is employee names and boxes H4-V5 are the times the business is open 8am-10pm. In the picture the graph is for Monday. I am trying to pull the scheduled shift of person BT from the Schedule sheet so that would be box E11 and then highlight the cells that correspond to that shift. So, it would highlight boxes H5-P5.
I decided that I will do one graph for each day of the week. I just cannot figure out the formula to make it happen. I know you could manually highlight this but am trying to have it auto update so the person doing the scheduling can concentrate on scheduling.
I was able to figure out the formula to calculate total employee hours took me a while, but I did it. Really having issues with this one.
=SUM(IF(Schedule!D11:J11="OFF", 0,
(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(MID(Schedule!D11:J11, FIND("-", Schedule!D11:J11) + 1, LEN(Schedule!D11:J11) - FIND("-", Schedule!D11:J11)), "am", " am"), "pm", " pm")) -
TIMEVALUE(SUBSTITUTE(SUBSTITUTE(LEFT(Schedule!D11:J11, FIND("-", Schedule!D11:J11) - 1), "am", " am"), "pm", " pm"))) * 24))
I expanded upon the work by m_tarler in the attached workbook, first showing how conditional formatting and Data Bars work for partial hours at the end of a workday, but not at the start. I also show an alternative that graphs partial hours whether at the start of the workday, the end of the workday, or both.
You did not mention which version of Excel you are using, or on which platform. The formulas in the attached workbook require Excel 365 or later (or possibly Excel for the web).
First I will thank SnowMan55 for updating/correcting my sheet. I knew there were a few errors/limits but wanted to get some feedback from OP before going forward but your updates inspired me to make another update. So, as another variant, building on SnowMan55 work, here is another way to use conditional formatting. Basically if it is a partial hour then show a gradient and the time in MIN:SEC for that partial hour:
so here is my suggested formula:
=LET(today,FILTER(Schedule,Schedule[#Headers]=$H$3), start,IFERROR(TIMEVALUE(TEXTBEFORE(today,"-")),""), end,IFERROR(TIMEVALUE(TEXTAFTER(today,"-")),""), (start<=H4#)*(end>=H4#))on the schedule tab I defined that table as "Schedule"
I also added cell H3 as how to define which day of the week to look at
H4# is the set of times which I used a formula to create those hour VALUES and then formatted as time
then I used conditional formatting using value bars to show which hours are used. You could alternatively do this all in a conditional formatting formula or format the text as white and then the conditional formatting is simply if value is 1 then fill blue and make text blue.
the part I didn't solve is for the 1/2 hour shifts. you could expand the table/graph to have 2-4 times more columns to account for those partial hours, that should work but I was hoping for some clever use of the bar graph to do the 1/2 or 1/4 or 3/4 values to give the partial bars which I could get to work on the end shift side but can't get the partial bar to look right on the start shift side so instead of a 1/2 solution I dropped it.
