Hi There,
I came across a wonderful youtube video (courtesy of Excelfun - MrExcel) explaining how to make a Gantt Chart - Staff workday chart using start and finish times with conditional formatting.
https://www.youtube.com/watch?v=qaisZI6i75U
The example was very clear and I was able to make a chart with start and finish times on the left and the day going horizontally across the sheet.
I am using Excel 2016
I altered the chart to contain staff names in Column A and also changed the hourly to include half hour increments and all was well.
However I wanted to change the chart to be vertical as it will print out nicely onto an A4 sheet; but for love nor money can I get it to work properly this way around!
Firstly I couldn't get a formula to work for the whole range (so that all staff work hours were formatted the same colour as per the example) but eventually
I figured a conditional formula that sort of works for each column separately (which actually is great because each staff member can be a different colour), but the problem is that it will not recognise 18:30!
If the member of staff works 15:00 to 19:00 then the chart formats correctly and those cells change colour, if they work 15:00 to 18:00 no problem...but if they work till 18:30 the formatting simply shades 15:00 to 18:00 only.
It works OK with every other half hour increment from 08:00 to 09:30 onwards
I am assuming that it simply is not recognising 18:30 from cell C5 and I do not know why, I have tried changing the time formats but this doesn't help
My Conditional formula is =AND($A6>=B$3,$A7<=B$5) - applied to range C6:C28
Row 1 - Contains the Day the Chart Applies to....Thursday 9th March
Row 2 - Staff Names running from Column B:J
Row 3 - Start Time
Row 4 - Number of hours
Row 5 - Finish Time
Column A - 6:28 = 08:00 to 19:00 in half hour increments
A6 is the first time: 08:00 AM and A7 is the second time: 08:30
B3 & B5 are Camilla's start and finish time,
(I should note that B3 has no formula, one enters the start time manually but C5 contains =IF(C4<4,C3+C4/24,C3+(C4/24)+(0.5/24))
basically B3 + B2, but if they work 4 hrs or less then they do not get a lunch break of half an hour)
Please help!
Many thanks
Kerstin
I came across a wonderful youtube video (courtesy of Excelfun - MrExcel) explaining how to make a Gantt Chart - Staff workday chart using start and finish times with conditional formatting.
https://www.youtube.com/watch?v=qaisZI6i75U
The example was very clear and I was able to make a chart with start and finish times on the left and the day going horizontally across the sheet.
I am using Excel 2016
I altered the chart to contain staff names in Column A and also changed the hourly to include half hour increments and all was well.
However I wanted to change the chart to be vertical as it will print out nicely onto an A4 sheet; but for love nor money can I get it to work properly this way around!
Firstly I couldn't get a formula to work for the whole range (so that all staff work hours were formatted the same colour as per the example) but eventually
I figured a conditional formula that sort of works for each column separately (which actually is great because each staff member can be a different colour), but the problem is that it will not recognise 18:30!
If the member of staff works 15:00 to 19:00 then the chart formats correctly and those cells change colour, if they work 15:00 to 18:00 no problem...but if they work till 18:30 the formatting simply shades 15:00 to 18:00 only.
It works OK with every other half hour increment from 08:00 to 09:30 onwards
I am assuming that it simply is not recognising 18:30 from cell C5 and I do not know why, I have tried changing the time formats but this doesn't help
My Conditional formula is =AND($A6>=B$3,$A7<=B$5) - applied to range C6:C28
Row 1 - Contains the Day the Chart Applies to....Thursday 9th March
Row 2 - Staff Names running from Column B:J
Row 3 - Start Time
Row 4 - Number of hours
Row 5 - Finish Time
Column A - 6:28 = 08:00 to 19:00 in half hour increments
A6 is the first time: 08:00 AM and A7 is the second time: 08:30
B3 & B5 are Camilla's start and finish time,
(I should note that B3 has no formula, one enters the start time manually but C5 contains =IF(C4<4,C3+C4/24,C3+(C4/24)+(0.5/24))
basically B3 + B2, but if they work 4 hrs or less then they do not get a lunch break of half an hour)
Please help!
Many thanks
Kerstin
