Process Time Management

[DJC3103]

Hi guys . . . I'm after some help / guidance..
I have 8.5 hours a day available (Monday to Thursday) and 4.5 for a Friday (07:00 - 15:30 & 07:00 - 11:30 respectively)
I need to plan in a number of activities that could span over multiple days (in hours & minutes)
I want to enter a start date with time - set the activity time frame (i.e 15 hours) and excel tell me the end time (accounting for the difference in Fridays) and accommodating the start/finish hours... and span over weekends also !!

I'm trying to build a production management tool for product throughput...

Any help would be greatly appreciated.

 

[Col Delane]

I'm a bit confused by this:

4.5 for a Friday (07:00 - 15:30 & 07:00 - 11:30 respectively)

Please expand.​

My initial thoughts (which is not necessarily the optimum method) re how to approach this are:

1. Your planning schedule table to have:
   1. the timeline (sequential dates) across the top, and
   2. Job ID (input) + Job Start Date/time (input) + Job duration (input) + Job End Date/time (calc from results returned by the following) down the left hand columns
2. The dates & times across the top would be in two rows:
   1. Shift Start date & time (format the cell to show both date & time) - these could be calc'd by formula from a timeline start date
   2. Shift End date & time (format the cell to show both date & time) - these could be calc'd by formula from a timeline start date
3. The next row would return the "Available Time" for each shift = [End date & time] less [Start date & time]
4. Now comes the hard/tricky bit!
   1. In each intersecting cell you need a rather long & complex formula that assesses whether the job starts in this shift (hrs = shift end date/time less job start date/time) OR traverses all of this shift (hrs = all shift time) or ends in this shift (hrs = job end date/time less shift start date/time). If none are True then return 0.
   2. Calculate the Job End Date/time cell (back on LHS) from the intersecting cells returning the last non-zero value. (If you're using Office 365 you could use the MAXIFS function to ascertain the Start date/time in the top row and then add the hours used from that last shift.)

An alternative to 2.2 & 3 is to input the Available Time (or use some type of lookup to return values from a table based on Weekday No) and then calculate the Shift End date & time = [Shift Start date & time] + [Available time]

Date/time arithmetic is tricky, so it'll take some cool thinking and good algebra.

Hope this helps.