Time diff cal

Please help me to calculate the time difference between two date which should not include (1. holidays, 2.Weekends, 3.non business work timing)
(e.g) start date & Time: 2/1/2010 10:23 AM
End date & Time: 2/3/2010 10:36 AM

It should calculate duration between the above mentioned date and timing

Kindly reply me back soon. Egarly waiting for your reply!

Thanks in advance!

Assuming you have start time/date in A2 and end time/date in B2 and that these are both within working hours try

=(NETWORKDAYS(A2,B2)-1,holidays)*("17:00"-"8:00")+MOD(B2,1)-MOD(A2,1)

where holidays is a named range containing holiday dates and Mon-Fri business hours are 08:00-17:00 (adjust as required)

Originally Posted by rwgrietveld

Use DLL's formula =NETWORKDAYS(A2,B2-1,holidays)*("21:30"-"12:30")+MOD(B2,1)-MOD(A2,1)

and put your holidays in a named range like suggested.

How to make a named Range:
A5: Easter
A6: Chistmas
A7: Newyear

B5: =DATE(2010,4,4)
B6: =DATE(2010,12,25)
B7: =DATE(2010,12,31)

make a named range "holidays" refers to B5:B7

I'm sorry to disturb u like this....

what is DLL's formula??????

Ricardo was referring to the formula I suggested. Unfortunately there's a typo in there (thanks for pointing that out Ricardo), apologies. It should be like this

=(NETWORKDAYS(A2,B2,holidays)-1)*("21:30"-"12:30")+MOD(B2,1)-MOD(A2,1)

where holidays is a named range with a list of holiday dates. You don't specifically need a named range - you can list holiday dates in H1:H10, for instance, and use this formula

=(NETWORKDAYS(A2,B2,$H$1:$H$10)-1)*("21:30"-"12:30")+MOD(B2,1)-MOD(A2,1)

If the formula is in C2 then make sure you custom format that cell with the format [h]:mm so that hours over 24 are correctly shown.

Note: as I said in my original post, A2 and B2 should always be within the working hours, i.e. 12:30 to 21:30 on working days otherwise the formula might not return the correct results. If that might not be the case post back, you can use a more complex formula to cater for start/end times outside business hours

Originally Posted by daddylonglegs

Ricardo was referring to the formula I suggested. Unfortunately there's a typo in there (thanks for pointing that out Ricardo), apologies. It should be like this

=(NETWORKDAYS(A2,B2,holidays)-1)*("21:30"-"12:30")+MOD(B2,1)-MOD(A2,1)

where holidays is a named range with a list of holiday dates. You don't specifically need a named range - you can list holiday dates in H1:H10, for instance, and use this formula

=(NETWORKDAYS(A2,B2,$H$1:$H$10)-1)*("21:30"-"12:30")+MOD(B2,1)-MOD(A2,1)

If the formula is in C2 then make sure you custom format that cell with the format [h]:mm so that hours over 24 are correctly shown.

Note: as I said in my original post, A2 and B2 should always be within the working hours, i.e. 12:30 to 21:30 on working days otherwise the formula might not return the correct results. If that might not be the case post back, you can use a more complex formula to cater for start/end times outside business hours

I'm getting the result but it is not correct!!!
it will be better if you assist me with the complex one!!!
please be specific and explain the complex formula, I need to calculate this for more than 1 data!!!
thank u so much for explaining and assist me!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Originally Posted by abirami

I'm getting the result but it is not correct!!!

Can you give me an example with start and end time where you got the wrong result?

This is the more complex version, A2 and B2 can be any time. J2 and J3 define the start and end times of the working day (so for your situation J2 should contain the time 12:30 and J3 21:30)

=(NETWORKDAYS(A2,B2,holidays)-1)*(J$3-J$2)+IF(NETWORKDAYS(B2,B2,holidays),MEDIAN(MOD(B2,1),J$3,J$2),J$3)-MEDIAN(NETWORKDAYS(A2,A2,holidays)*MOD(A2,1),J$3,J$2)

Originally Posted by daddylonglegs

Can you give me an example with start and end time where you got the wrong result?

This is the more complex version, A2 and B2 can be any time. J2 and J3 define the start and end times of the working day (so for your situation J2 should contain the time 12:30 and J3 21:30)

=(NETWORKDAYS(A2,B2,holidays)-1)*(J$3-J$2)+IF(NETWORKDAYS(B2,B2,holidays),MEDIAN(MOD(B2,1),J$3,J$2),J$3)-MEDIAN(NETWORKDAYS(A2,A2,holidays)*MOD(A2,1),J$3,J$2)

Start time: 1/28/2010 2:51:46 PM
End Time: 1/28/2010 2:52:00 PM
please let me know the format for this date cells(A2, B2)

I think it will work if i change the format!!!

As long as the date/times are in a valid date/time format (i.e. they are not text formatted) then it doesn't matter what the format is. If the dates are formattable then they are valid.

For your example the result is just 14 seconds isn't it? so, of course you'd need to format the result cell to see seconds, i.e. [h]:mm:ss but both formulas should give the result 0:00:14, what do you get?

Originally Posted by daddylonglegs

As long as the date/times are in a valid date/time format (i.e. they are not text formatted) then it doesn't matter what the format is. If the dates are formattable then they are valid.

For your example the result is just 14 seconds isn't it? so, of course you'd need to format the result cell to see seconds, i.e. [h]:mm:ss but both formulas should give the result 0:00:14, what do you get?

Yes, I'm getting 14 seconds.
But for this start date: 1/18/2010 12:53:26 PM
End date: 1/18/2010 12:53:00 PM

I'm not getting result!!!

Originally Posted by rwgrietveld

Maybe not the one you expect, but definitely a result I guess.

What is that result? #VALUE!

no only ##############

Originally Posted by abirami

Yes, I'm getting 14 seconds.
But for this start date: 1/18/2010 12:53:26 PM
End date: 1/18/2010 12:53:00 PM

I'm not getting result!!!

How can it end before it starts?

What result do you expect here, a negative time value? Excel doesn't like those much but you can work around it, but the formula I suggested would need to be tweaked......

Can you give an overview of what you are trying to accomplish here? It seems odd to me that you want a formula that counts across weekends, holidays and business days etc. but you are now testing it on time periods of a few seconds and start times after end times, does your data actually contain these very short time periods or are you just testing very thoroughly?

To be clear. The last formula I suggested works with start and end times which can be any time, even on holidays or at weekends, evenings etc.....but end time date must be greater than or equal to start time/date

Originally Posted by daddylonglegs

How can it end before it starts?

What result do you expect here, a negative time value? Excel doesn't like those much but you can work around it, but the formula I suggested would need to be tweaked......

Can you give an overview of what you are trying to accomplish here? It seems odd to me that you want a formula that counts across weekends, holidays and business days etc. but you are now testing it on time periods of a few seconds and start times after end times, does your data actually contain these very short time periods or are you just testing very thoroughly?

To be clear. The last formula I suggested works with start and end times which can be any time, even on holidays or at weekends, evenings etc.....but end time date must be greater than or equal to start time/date

Hi,

Thank u for ur great help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1
Your last formula is working for me.
once again a bunch of thanks to uuuuuuuuuuuuuuuuuuuuuuu