Formula to substract a range of time from a range of time

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Hi Glenn,

Thanks for the reply and apologies from my end for responding to you late on this. I am attaching a sample sheet which has the following info.

1- Start Downtime
2- End Downtime
3- Total Duration of downtime.

24 Hours Breakup of On and Off Production

1- Production hours time.
2- Off Production Hours time.

Requirement:

1- Formula should be able to calculate the actual on production hours downtime during the complete duration.
2- Formula should be able to calculate the actual off production hours downtime during the complete duration.
3- Some accounts off production times will vary for e.g some will be completely switched off on Sat and Sun, some will be working on Sat with less number of production hours etc.



I have put in only 2 rows which are the two scenarios i have, once i have the formula, i can extend it through out the worksheet. I would really appreciate if the formula could be changed according to the working days and hours.

I really really appreciate the help from your side. Thanks a lot!

Hello Nabeel and Welcome to Excel Forum.
This proposed solution employs a helper table and a change in the format of the production hours table, highlighted in green.
The formula that populates the helper table is:

Formula:

=IFERROR(IF(WEEKDAY(H$1,2)<=5,IF(ROUND($B2,0)=H$1,MIN(ROUND($B2+1,0)-$B2,$C2-$B2)-IF(MOD($B2,1)<$E$12,$E$12-MOD($B2,1),0),IF(ROUND($C2,0)=H$1,IF(MOD($C2,1)<$E$13,MOD($C2,1),IF(MOD($C2,1)>=$E$12,MOD($C2,1)-($E$12-$E$13),$E$13)),IF(ROUND($C2,0)>H$1,1-$E$12+$E$13,""))),IF(ROUND($B2,0)=H$1,MIN(ROUND($B2+1,0)-$B2,$C2-$B2)-IF(MOD($B2,1)<$E$14,$E$14-MOD($B2,1),0),IF(ROUND($C2,0)=H$1,IF(MOD($C2,1)<$E$15,MOD($C2,1),IF(MOD($C2,1)>=$E$14,MOD($C2,1)-$E$14,$E$15)),IF(ROUND($C2,0)>H$1,1-$E$14+$E$15,"")))),"")

Note: The days listed in row 4 are not needed, they are just there to indicate which dates in row one (necessary) are weekend dates.
The dates in row 1, beginning with column H, are generated using the formulas and will adjust as the start and end dates for the projects are adjusted.
The formula that populates column E is: =SUM(H2:AC2)
The formula that populates column F is: =D2-E2
Columns E and F are custom formatted [h]:mm "Hours"
Let us know if you have any questions.

Hi Jete,

Wow, this is great!!! Thanks a lot for the help. This is exactly what i needed. There is one more requirement though as i forgot to mention.

This sheet is great as it is, but some of the accounts have different production hours on Sat and Sun, weekdays remain the same as it is but some have same weekend timings to which this spreadsheet will fit in perfectly! but for others, sat and sunday has a different slot of production timings.
Will this be able to cater for different Sat and Sunday timings too or would need some tweaks on the formula?



I really appreciate the help you have extended. Would really appreciate it, if you can address the different production hours issue on Sat and Sun on the same sheet please?

Regards

Nabeel ur rehman.

Hello Nabeel,

It would help if you could upload a sample spreadsheet showing examples of accounts that have different production hours for the weekends. Also please include an example of where the formula gives incorrect on and off production timings based on PM start time and AM end time.

As to post #8 where the production hours for Saturday are different than the production hours for Sunday a possible solution is make an adjustment to the green highlighted table and add another conditional layer to the formula as in:

Formula:

=IFERROR(IF(WEEKDAY(H$1,2)<=5,IF(ROUND($B2,0)=H$1,MIN(ROUND($B2+1,0)-$B2,$C2-$B2)-IF(MOD($B2,1)<$E$13,$E$13-MOD($B2,1),0),IF(ROUND($C2,0)=H$1,IF(MOD($C2,1)<$E$14,MOD($C2,1),IF(MOD($C2,1)>=$E$13,MOD($C2,1)-($E$13-$E$14),$E$14)),IF(ROUND($C2,0)>H$1,1-$E$13+$E$14,""))),IF(WEEKDAY(H$1,2)=6,IF(ROUND($B2,0)=H$1,MIN(ROUND($B2+1,0)-$B2,$C2-$B2)-IF(MOD($B2,1)<$E$15,$E$15-MOD($B2,1),0),IF(ROUND($C2,0)=H$1,IF(MOD($C2,1)<$E$16,MOD($C2,1),IF(MOD($C2,1)>=$E$15,MOD($C2,1)-$E$15,$E$16)),IF(ROUND($C2,0)>H$1,1-$E$15+$E$16,""))),IF(ROUND($B2,0)=H$1,MIN(ROUND($B2+1,0)-$B2,$C2-$B2)-IF(MOD($B2,1)<$E$17,$E$17-MOD($B2,1),0),IF(ROUND($C2,0)=H$1,IF(MOD($C2,1)<$E$18,MOD($C2,1),IF(MOD($C2,1)>=$E$17,MOD($C2,1)-$E$17,$E$18)),IF(ROUND($C2,0)>H$1,1-$E$17+$E$18,""))))),"") 

As to post #9 where the time has been changed to 2:00 PM, I'll have to look at that a little closer and try and get back to you with a possible solution.

The formula used in the attached file seems to have solved the issue of end times being changed from AM to PM. This formula is written in such a way that you will need to schedule Saturday's and Sunday's times separately even if they are the same, as seen in the attachment. Please test the formula rigorously, as I have only tested minimally.
Let us know if you have any questions.

Hello Nabeel,

Please upload the spreadsheet from which the screen shot was taken.

Let us know if you have any questions.

Please see if the results are as expected. I had to make an adjustment to the formula, adding =IF(H$9< ROUND($B10,0),"",... to the beginning.
Let us know if you have any questions.

Hi Jete,

Once i add more data rows below for eg. b39 and C39, the on production still shows zero while off production shows the whole duration hours. Attached is the file.

The problem that i seem to be having is, when i add more data rows, i can extend the formula in the adjacent cells i.e. columns C,D,E,F which is working. But for the formula columns which currently are ending on column EC, if i add another data row that formula should get extended to ED,EF,EG accordingly and i can get the formula extended in the column vertically but it shows blank after EC.

The range of the formula has been extended horizontally to column NZ so that it will accommodate 383 days between the minimum date in column B and the maximum date in column C. The range has also been extended vertically to row 100.
The array entered formula* in cell H9 has been changed so that it reads:

Formula:

=ROUND(MIN(IF(B10:B5000>0,B10:B5000)),0)

*Remember that array entered formulas need to be activated by simultaneously pressing the Ctrl, Shift and Enter keys while the cell is in edit mode.
Likewise the formula in I9 has been changed to read:

Formula:

=IFERROR(IF(H9+1>MAX($C10:$C5000),"",H9+1),"")

The formula is then copied out to column NZ also.
If you need to extend the start and end times further than row 5000 those two formulas will need to be changed again to increase the range.
If you need to have a longer range of dates than 383 days you can select the range NZ9:NZ100 and drag the fill handle to the right as far as needed.
Note that extending the range which is populated by the formula will increase the size of the file as may be evidenced by the fact that the changes made to the file since it was attached to to post #16 is approximately five fold (200KB).
Let us know if you have any questions.