Hi, Myrna--
Thanks for the suggestions! Sorry about the late reply, but I finally =
gave up=20
on the thing last night and just went to bed. I'm goiing to take what =
you've=20
offered and see if I can get it to work in my sheet--I'll let you know =
how it=20
all comes out. Thanks!
-----Original Message-----
Hi, Jason:
The formula the OP posted uses the number 24, implying a 24-hour=20
workday.
Somehow I can't really believe that, and in support of my scepticism, =
he=20
says
there are workday start and ending times in H5 and H6.
As for your formula, I don't think you've handled the possibility that =
the
dates in A1 and B1 not dates and times during a workday, i.e. they are
holidays or weekend days, or (if it's not a 24-hr workday) they are =
times
outside the workday.
Assuming you want hours worked, and it's not a 24-hour workday, you=20
can get
the number of hours worked from days 2-(N-1) this way:
=3DNETWORKDAYS(A1+1,B1-1,J1:J10)*(H6-H5)*24
I think the correct formula for the hours on the 1st date is
=3DIF(NETWORKDAYS(A1,A1,J1:J10)=3D1,(H6-MAX(H5,MOD(A1,1)))*24,0)
and for hours on the last date
=3DIF(NETWORKDAYS(B1,B1,J1:J10)=3D1,(MIN(H6,MOD(B1 ,1))-H5)*24,0)
Those 3 have to be combined into 1 formula to get the total.
Here is some VBA code that I developed for this problem. I avoided the=20
need
for the ATP by "rolling my own" substitute for the NETWORKDAYS =
function.=20
That
probably slows it down, so it wouldn't be good for a long time span=20
measured
in years. It could be rewritten to use NETWORKDAYS.
The start and end of the workday are hard-coded as 9AM and 5PM. It also
handles the start and end dates being on the same date (presumably=20
different
times).
Option Explicit
=20
Function WorkedHours(Date1 As Double, Date2 As Double, _
Optional Holidays As Range =3D Nothing) As Double
Dim D As Long
Dim D1 As Long
Dim D2 As Long
Dim T1 As Double
Dim T2 As Double
Dim Total As Double
=20
Const DayStart As Double =3D 9 / 24
Const DayEnd As Double =3D 17 / 24
Const FullDay As Double =3D DayEnd - DayStart
=20
D1 =3D Fix(Date1)
T1 =3D Date1 - D1
If T1 =3D 0 Then
'no time given: default to START of workday
T1 =3D DayStart
Else
'adjust to time within the workday
If T1 < DayStart Then T1 =3D DayStart
If T1 DayEnd Then T1 =3D DayEnd
End If
=20
D2 =3D Fix(Date2)
T2 =3D Date2 - D2
If T2 =3D 0 Then
'no time given: default to END of workday
T2 =3D DayEnd
Else
'adjust to time within the workday
If T2 < DayStart Then T2 =3D DayStart
If T2 DayEnd Then T2 =3D DayEnd
End If
=20
Total =3D 0
If D2 =3D D1 Then
If IsWorkday(D1, Holidays) Then Total =3D T2 - T1
Else
For D =3D D1 To D2
If IsWorkday(D, Holidays) Then
Select Case D
Case D1 'start date
Total =3D Total + (DayEnd - T1)
Case D2 'end date
Total =3D Total + (T2 - DayStart)
Case Else 'days between
Total =3D Total + FullDay
End Select
End If
Next D
End If
=20
WorkedHours =3D Total
=20
End Function
=20
Private Function IsWorkday(Dt As Long, _
Optional Holidays As Range =3D Nothing) As Boolean
IsWorkday =3D False
If Dt Mod 7 =3D 2 Then 'N.B. Sunday - 1, Saturday - 0
If Holidays Is Nothing Then
IsWorkday =3D True
Else
IsWorkday =3D IsError(Application.Match(Dt, Holidays, 0))
End If
End If
End Function
On Thu, 17 Feb 2005 19:56:41 -0800, "Jason Morin"
wrote:
Try:=20
=3DNETWORKDAYS(A1,B1,J1:J10)-2+(1-MOD(A1,1))+MOD(B1,1)=20
where J1:J10 is your list of holiday dates, A1 is the=20
start date/time, and B1 is the end date/time. You can=20
simplify the formula further using:=20
=3DNETWORKDAYS(A1,B1,J1:J10)-1-MOD(A1,1)+MOD(B1,1)=20
Make sure the analysis toolpak is installed under Tools=20
Add-Ins. Otherwise you'll get a #NAME? error.
HTH=20
Jason=20
Atlanta, GA=20
-----Original Message-----
I have no idea where I've gone wrong. Given the=20
following=20
cells:
J13=3D Start Date & Time
K13=3DEnd Date & Time
AE66:AE83=3DRange of non-working holidays
H5=3D Start of workday (a time)
H6=3DEnd of workday (also a time)
I've evolved (with a lot of help) the followng formula.=20
It is supposed to return the number of workdays and=20
workhours between the start date and time and the end=20
date and time. Much of the time it works pretty well,=20
but=20
too often I'm getting negative hour counts. I have no=20
idea where I've gone wrong. HELP! I'm desperate and=20
tired. Here's the formula:
=3DIF(AND(INT(J13)=3DINT(K13),NOT(ISNA(MATCH(IN T(J13),
AE66:AE83,0)))),"0 days 0 hours", IF(INT(J13)=3DINT(K13),
"0 days " & ROUND(24*(K13-J13),2)&"hours",
MAX(NETWORKDAYS(J13+1,K13-1,AE66:AE83),0)+
INT(24*(((K13-INT(K13))-(J13-INT(J13)))+
('H6'-H5))/(24*('H6'-H5)))&
" days "&MOD(ROUND(((24*(K13-INT(K13)))-24*H5)+
(24*'H6'-(24*(J13-INT(J13)))),2),
ROUND((24*('H6'-H5)),2))&" hours "))
Does anyone know how I can rewrite this to make it=20
consistent? All I need is, for a wide variety of=20
starting and ending dates/times, to calculate the number=20
of workdays and workhours between the two.
.
.
|