Gantt chart in hours using conditional formatting

Hi all,

Firstly this is my first post so go easy on me! Secondly, apologies if this is in the wrong section - it does not really fit in charting as I am not using charts to create my "Gantt chart".

I work in manufacturing and I have created a daily production plan by quarter as a Gantt chart (using conditional formatting) which I have used for a couple of years and has evolved during that time. The problem I am struggling with is that I now want to create a similar plan but by hours, not days. The reason for this is that Production have moved on to an annualised hours work calendar which means they work shorter days in the first part of the year and longer days in the second half. This creates a varying cycle time which I need to account for in my plan.

I will attempt to attach an example spreadsheet but here is a brief description of how the daily chart works (Plan by Days sheet):
Columns C to O have start and finish dates for each process (Order release, Pick, Build, Pre-Test, Test, Dispatch, Shipping). All of these dates are automatically populated by the first Build Finish date and take into account working days, holidays etc. The second build finish date is calculated as 1 day after the first build finish date. The idea is that I can change one date (the build finish date) and the whole plan will change. These use the Workday.intl function as Production are currently working a 4 day week.
Ok, so now I have the start and finish dates for each process, I now want to display that visually so to the right of the dates (P8 to DX54) I have a top row (P7 to DX7) with all the days of the quarter and use a formula in each cell of the "chart" to check if the date matches any of the process start/finish dates in the columns on the left. The cell will display a letter based on this i.e. "P" for Pre-Test. I then use conditional formatting to change the colour of the cell and text depending on it's contents. This then provides a Gantt chart effect using conditional formatting.

Ok, so here is the problem. I now need to create a similar plan but using hours (Plan by hours sheet), but to further complicate this I want to be able to easily vary the hours or number of days worked depending on the quarter that I am planning. i.e. Q1 needs to have e 4 day week (Mon-Thur) and only 7.5 hours per day but in Q4 we may work 5 day weeks and 10 hour days.
Specific problems: I want to be able to enter one date and time (Finish Ship Date) and for all other process start/end times to be calculated backwards based on each process cycle time in hours (row 4) and based on 7.5 hour working days over 4 days/week.
I am struggling with adding/subtracting hours which are over 24 hours.
Also, was wondering if it is possible to set up some sort of calendar which may simplify the formulas. I will need to be able to change this as the working hours change per quarter.
I don't have much experience working with time or calendars in Excel.
I would prefer not to use VBA as I have even less experience in this than in time or calendars!

Thanks in advance for any help received!
Mark.

Thanks for the response.
I like your solution for the 4 or 5 day week - I couldn't get it to work as a cell reference before but you putting ` in front of the day reference seems to have done the trick!

Breaking it down into hours as a proportion of a full day does not do what I need though. I have tried this before but the workday function only seems to like integers. For example on a 10 hour day, if the cycle time was set to 19 hours, it counts this as 1 day (as it's less than 2), but if you go to a cycle time of 20 it will count as 2 days. This is why I have been heading down the hours route.

I think the key question for me to get this to work is to figure out how to count backwards in hours from a starting date and time. I can only get it to work when counting back within the same date.

Thanks
Mark.

Thanks loginjmor.

I have played around with using a formula to calculate the hours as a proportion of days but I still hit the problem of the Workday.intl function not working with fractions of days. The formula works but anything over 1 day turns into 2 days so this solution falsely increases my overall cycle time.

Do you have any suggestions on how to overcome this? I was thinking of writing a formula which looks at the total cycle time in hours and then breaks down each process into a proportion of the total time in days but does not exceed the total, rather than converting each process from hours to days and adding them together which gives the cumulative increase to my total cycle time. My brain has stopped working now and I can't figure out a way of doing it at the moment!

p.s. How did you format your `0000111 cell in the example spreadsheet as that didn't work in my master sheet? The only way I could get it to work was to format the cell as text rather than general.