Calculate the time difference excluding weekends and out of businesshours ( i.e. 8AM - 5 PM)

  • Thread starter Thread starter joshi.sadanand
  • Start date Start date
J

joshi.sadanand

Following are my values:-
A1= 07/11/08 09:00
A2= 07/21/08 11:00
B1= 08:00
B2= 17:00

I dont have any holidays within this period. But when I try to use the
formula, which I could see in another discussion:-

A3=(NETWORKDAYS(A1,A2,C1:C8)-1)*(B2-
B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1),B2)-
MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1)

then I am getting the answer as 2.33 ( which seems to be incorrect
here).
Could you please guide me where I am making mistake?
 
Following are my values:-
A1= 07/11/08 09:00
A2= 07/21/08 11:00
B1= 08:00
B2= 17:00

I dont have any holidays within this period. But when I try to use the
formula, which I could see in another discussion:-

A3=(NETWORKDAYS(A1,A2,C1:C8)-1)*(B2-
B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1),B2)-
MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1)

then I am getting the answer as 2.33 ( which seems to be incorrect
here).
Could you please guide me where I am making mistake?
Click to expand...

Just forgot to mention, I am trying to calculate the difference in
these 2 timestamps excluding the weekends, public holidays and out-of-
business hours.
 
If you are looking for the answer as a time, either format as [h]:mm to get
a display 56:00, or multiply by 24 if you want decimal hours (56).
--
David Biddulph

Following are my values:-
A1= 07/11/08 09:00
A2= 07/21/08 11:00
B1= 08:00
B2= 17:00

I dont have any holidays within this period. But when I try to use the
formula, which I could see in another discussion:-

A3=(NETWORKDAYS(A1,A2,C1:C8)-1)*(B2-
B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1),B2)-
MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1)

then I am getting the answer as 2.33 ( which seems to be incorrect
here).
Could you please guide me where I am making mistake?
Click to expand...

Just forgot to mention, I am trying to calculate the difference in
these 2 timestamps excluding the weekends, public holidays and out-of-
business hours.
 
If you are looking for the answer as a time, either format as [h]:mm to get
a display 56:00, or multiply by 24 if you want decimal hours (56).
--
David Biddulph


Following are my values:-
A1= 07/11/08 09:00
A2= 07/21/08 11:00
B1= 08:00
B2= 17:00
Click to expand...
I dont have any holidays within this period. But when I try to use the
formula, which I could see in another discussion:-

then I am getting the answer as 2.33 ( which seems to be incorrect
here).
Could you please guide me where I am making mistake?
Click to expand...

Just forgot to mention, I am trying to calculate the difference in
these 2 timestamps excluding the weekends, public holidays and out-of-
business hours.- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks David for your quick help! It worked!!
 
Back
Top