Calculating Elapsed Time - but not overnight.

Hi,

I thought I would find plenty of help for this around the web and in these forums, but alas, I have not.

What I need to do is calculate the number of hours between two times, but only within a set 13 hour business day. 8am-9pm 7 days a week.

I have used networkdays before, but that excludes weekends.

For example, Monday 8am till Thursday 11pm, calculate the number of hourse and minutes. BUT exclude each night's hours between 9pm-8am.

Hope that makes sense!

Thanks for ANY help.

narelle.

thanks Arthur, that worked perfectly. You make it look easy!

Glad to help - Thx for the feedback

Hi again.

So it isn't working on a few further examples that I tested this morning. It seems to be failing when the start time is after hours.

For example:

Start : 11/10/2006 11:37:00 PM
Finish : 16/10/2006 1:15 PM


The answer being returned (in the custom format of [hh]:mm ) is 54:38 . The answer should be around 57 hours. It seems to be subtracting the number of hours between 9pm-11.37pm

I might have to use IF formulas to look for after hours start times, and so forth.

My other helper thinks he has it with all complicated IF functions, but yours looks simpler!

thanks,
narelle

your question appears strange. if you want to do the calculation on the basis of the start and finish times being in the range of your business hours it is much simpler, otherwise it is a complicated if function! Can the finish be outside your work hours, or even the start, which you give in your example

Regards

Dav

Thanks for the response Dave. Yes it is a tricky one. It is for IT support purposes, and these requests for help can be logged after hours, even though the 'clock' for their service won't start until 8am the next morning.

Complicated indeed.

Not sure if this is the simplest formula, but I have tweeked the earlier one suggested and it appears to work, guess it could be simplified, but it is functional

=IF(HOUR(G11)<8,TRUNC(G11)+1/3,IF(HOUR(G11)>21,TRUNC(G11)+0.875,G11))-IF(HOUR(F11)<8,TRUNC(F11)+1/3,IF(HOUR(F11)>21,TRUNC(F11)+0.875,F11))-TIME(11,0,0)*VALUE(TRUNC(IF(HOUR(G11)<8,TRUNC(G11)+1/3,IF(HOUR(G11)>21,TRUNC(G11)+0.875,G11)))-TRUNC(IF(HOUR(F11)<8,TRUNC(F11)+1/3,IF(HOUR(F11)>21,TRUNC(F11)+0.875,F11))))

Regards

Dav

Hello waytoblue,

If you have start time and date in A2, end time and date in B2 and your daily start time (08:00) in F2 and daily end time (21:00) in F3 you can use this formula

=(INT(B2)-INT(A2))*($F$3-$F$2)+MEDIAN(MOD(B2,1),$F$2,$F$3)-MEDIAN(MOD(A2,1),$F$2,$F$3)

format result cell as [h]:mm

Thank you for both of the responses, and taking the time to work out a formula. I went with daddylonglegs formula, as it was less complicated and that worked fine. I haven't tested it on plenty of examples yet, but all good so far!

Thanks,
Narelle

yes the other solution is way more elegant, use it instead! Good work daddylonglegs!

Regards

Dav