Excel 2007 : Specific time period calculation from a time range

[Khaldon]

From the attached sheet, I would like help please in a formula to extract time value for times that falls between 18:00 and 0600 next day from cells A and B and have the result in Column D cells. as for the attached example, the requested formula should result in 4:05 in cell D1, and 5:42 in cell D2.

[Cutter]

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)

[Armandog]

Pardon my ignorance guys, but isn't the difference between 4:40 PM and 10:05 PM on the same day a total of 5 hours and 25 minutes?

I am delaing with a similar excel problem and came upon this thread. Cutter, I am trying to figure out if your solutiong might help with my issue. Thanks, and sorry for the intromision.

Start End Diff Expected
04/01/2011 16:40 04/01/2011 22:05 5:25 4:05
05/01/2011 00:18 05/01/2011 08:42 8:24 5:42

[Cutter]

@Armandog
The OP in this thread doesn't want

the difference between 4:40 PM and 10:05 PM on the same day

he/she wants

to extract time value for times that falls between 18:00 and 0600 next day

or the total time that was NOT between 6:00 and 18:00 of the same day.

[Khaldon]

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]

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

Try this amended formula:

Please Login or Register  to view this content.

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

[Khaldon]

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]

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

[Khaldon]

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]

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.

[Khaldon]

Splendid, thank you so much.

[Cutter]

You're welcome. Thanks for the "scales tap".