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

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?

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

On Jul 28, 1:45*pm, wrote:
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?

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.

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

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

wrote in message
...
On Jul 28, 1:45 pm, wrote:
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?

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.

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

On Jul 28, 3:46*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
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

wrote in message

...
On Jul 28, 1:45 pm, wrote:





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?

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 -

Thanks David for your quick help! It worked!!