Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date/Time Difference Excluding Weekends & Holidays | Excel Worksheet Functions | |||
Calculate A Date Excluding Weekends And Holidays | New Users to Excel | |||
Subtracting Dates to get total time work time excluding weekends | Excel Discussion (Misc queries) | |||
Calculate number of hours between dates and times excluding Weekends | Excel Discussion (Misc queries) | |||
Calculate A Date Excluding Weekends And Holidays | New Users to Excel |