Hi Myrna!
I think you need a check that the starting and ending
dates are in fact workdays
Yes, I agree that would be a good thing to do!
I came up with this more robust version but I only call
Networkdays once:
=SUM((18/24-MOD(A1,1))*(WEEKDAY(A1,2)<6),MOD(A15,1)-8/24*
(WEEKDAY(A15,2)<6)+(NETWORKDAYS(A1,A15)-SUM(WEEKDAY(A1,2)
<6,WEEKDAY(A15,2)<6))*10/24)
I'm using a time range of 8:00 to 18:00 with start
date/time in A1, end date/time in A15 for testing.
It's longer and kind of ugly but it works. I do like the
simplicity of your formula, however, multiple calls to
Networkdays results in #NAME? if you are troubleshooting
and using Evaluate Formula.
Biff
-----Original Message-----
I'm a bit more concerned about user input errors. I think
you need a check
that the starting and ending dates are in fact workdays,
i.e,
=IF(NETWORKDAYS(A1,A1)=1,18/24-MOD(A1,1),0)
+IF(NETWORKDAYS(B1,B1)=1,MOD(B1,1)-8/24,0)
+NETWORKDAYS(A1+1,B1-1)*9/24
On Tue, 22 Mar 2005 23:29:08 -0800, "Biff"
wrote:
Hmmm...
That can be slightly shortened to:
=SUM(18/24-(A1-INT(A1)),(B1-INT(B1))-8/24)+(NETWORKDAYS
(A1,B1)-2)*9/24
Biff
-----Original Message-----
Hi!
Do you want to exclude any holidays that fall on
regular
business days?
A1 = 17/12/2004 10:07
B1 = 3/02/2005 15:22
=SUM(17.5/24-MOD(A1,INT(A1)),MOD(B1,INT(B1))-8.5/24)+
(NETWORKDAYS(A1,B1)-2)*9/24
Format the cell as [h]:mm.
If you want to exclude holidays you need to make a list
of
holidays and then include a reference to that list as a
3rd argument in the NETWORKDAYS function.
Example:
Say you list holidays in the range C1:C10.
Include that range in the NETWORKDAYS function:
.....NETWORKDAYS(A1,B1,C1:C10)....
Biff
-----Original Message-----
Hi
I am trying to calculate the elspased time between
certain dates and times
with a result in total hours.
The catch is that I only want the formula to inlcude
times during business
hours (Monday to Friday, 8:30am - 5:30pm)
E.g (17/12/2004 10:07) - (3/02/2005 15:22) = ???
Is this possible with excel?
Many thanks
.
.
.
|