Specific time period calculation from a time range
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.
Last edited by Khaldon; 05-20-2011 at 11:06 AM.
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)
Last edited by Cutter; 05-18-2011 at 05:39 PM. Reason: Amended formula
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.
I didn't test the formula I gave you beyond the 2 examples you provided.
Try this amended formula:
AND
=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)))
to blank a result of 0 change the custom format from [h]:mm to [h]:mm;;""
Last edited by Cutter; 05-19-2011 at 08:26 AM. Reason: Amended formula
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.
Last edited by Khaldon; 05-19-2011 at 07:13 PM.
No, I meant it to be 17.
Have you tested it with times from 18:00 to 18:59?
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
@Armandog
The OP in this thread doesn't want
he/she wantsthe difference between 4:40 PM and 10:05 PM on the same day
or the total time that was NOT between 6:00 and 18:00 of the same day.to extract time value for times that falls between 18:00 and 0600 next day
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks