Excel 2007 : Specific time period calculation from a time range

**Cutter** · 05-18-2011, 05:27 PM

I'm sure there's a better way but try:

=IF(HOUR(A2)<6,6/24-(A2-INT(A2)),0)+IF(HOUR(B2)>17,B2-INT(B2)-18/24,0)

**Khaldon** · 05-18-2011, 08:33 PM

Thank you for the formula, however I have three comments if I may:
First: The times results are not correct when both the starting and end times in A and B cells happen to fall between 18:00 and 06:00.
Second: 6:00 will result when no time values are in cell A and B instead of blank!
Thirdly: 0 will result if both starting and ending times falls outside 18:00 and 6:00 instead of blank.
Please see new attached sheet.

**Cutter** · 05-19-2011, 07:33 AM

I didn't test the formula I gave you beyond the 2 examples you provided.

Try this amended formula:

=IF(B6="","",IF(OR(AND(HOUR(A6)<6,B6<=INT(A6)+6/24),AND(HOUR(A6)>17,B6>INT(A6)+18/24)),B6-A6,IF(HOUR(A6)<6,6/24-(A6-INT(A6)),0)+IF(HOUR(B6)>17,B6-INT(B6)-18/24,0)))

AND
to blank a result of 0 change the custom format from [h]:mm to [h]:mm;;""

**Khaldon** · 05-19-2011, 07:10 PM

Well done, please correct me if I was wrong, I did a little of modification to your formula, I changed all the 17 numbers to 18 in the formula which I think it was missed typed.

**Cutter** · 05-19-2011, 08:04 PM

No, I meant it to be 17.
Have you tested it with times from 18:00 to 18:59?

**Khaldon** · 05-20-2011, 03:55 AM

I did test it with times from 18:00 to 18:59 with your original formula after I undo my modification, formula original 17 number is back instead of my modified 18 and it resulted with: 59 which is fine.
Please excuse my ignorance in this part but why the 17 instead of 18 in some parts of the formula and not all of it, if I understand correctly 18 represent time 18:00 in the formula, would you be so kind to elaborate on this issue.

**Cutter** · 05-20-2011, 09:50 AM

The reason for the >17 is because you want to deal with times occurring after 18:00. Keep in mind that it is HOUR()>17. If you had >18 you would miss all times from 18:00-18:59.
The part that has 18/24 is being added to the cell's date to give a date and time for comparison purposes. I left it as 18/24 (instead of .75 or 3/4) so that it would be more easily recognized as referring to 6pm or 18:00 hrs.