Hi Myrna-
Yes, I was thinking total hours between 2 dates rather
without regard to workdays not being 24 hours. Another
person just posted a question about total hours between 2
dates, using 24-hour workdays and no weekends/holidays.
Jason
-----Original Message-----
Hi, Jason:
The formula the OP posted uses the number 24, implying a
24-hour workday.
Somehow I can't really believe that, and in support of
my scepticism, he 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 can get
the number of hours worked from days 2-(N-1) this way:
=NETWORKDAYS(A1+1,B1-1,J1:J10)*(H6-H5)*24
I think the correct formula for the hours on the 1st
date is
=IF(NETWORKDAYS(A1,A1,J1:J10)=1,(H6-MAX(H5,MOD(A1,1)))
*24,0)
and for hours on the last date
=IF(NETWORKDAYS(B1,B1,J1:J10)=1,(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 need
for the ATP by "rolling my own" substitute for the
NETWORKDAYS function. That
probably slows it down, so it wouldn't be good for a
long time span 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 different
times).
Option Explicit
Function WorkedHours(Date1 As Double, Date2 As Double,
_
Optional Holidays As Range = 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
Const DayStart As Double = 9 / 24
Const DayEnd As Double = 17 / 24
Const FullDay As Double = DayEnd - DayStart
D1 = Fix(Date1)
T1 = Date1 - D1
If T1 = 0 Then
'no time given: default to START of workday
T1 = DayStart
Else
'adjust to time within the workday
If T1 < DayStart Then T1 = DayStart
If T1 DayEnd Then T1 = DayEnd
End If
D2 = Fix(Date2)
T2 = Date2 - D2
If T2 = 0 Then
'no time given: default to END of workday
T2 = DayEnd
Else
'adjust to time within the workday
If T2 < DayStart Then T2 = DayStart
If T2 DayEnd Then T2 = DayEnd
End If
Total = 0
If D2 = D1 Then
If IsWorkday(D1, Holidays) Then Total = T2 - T1
Else
For D = D1 To D2
If IsWorkday(D, Holidays) Then
Select Case D
Case D1 'start date
Total = Total + (DayEnd - T1)
Case D2 'end date
Total = Total + (T2 - DayStart)
Case Else 'days between
Total = Total + FullDay
End Select
End If
Next D
End If
WorkedHours = Total
End Function
Private Function IsWorkday(Dt As Long, _
Optional Holidays As Range = Nothing) As Boolean
IsWorkday = False
If Dt Mod 7 = 2 Then 'N.B. Sunday - 1, Saturday -
0
If Holidays Is Nothing Then
IsWorkday = True
Else
IsWorkday = 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:
=NETWORKDAYS(A1,B1,J1:J10)-2+(1-MOD(A1,1))+MOD(B1,1)
where J1:J10 is your list of holiday dates, A1 is the
start date/time, and B1 is the end date/time. You can
simplify the formula further using:
=NETWORKDAYS(A1,B1,J1:J10)-1-MOD(A1,1)+MOD(B1,1)
Make sure the analysis toolpak is installed under Tools
Add-Ins. Otherwise you'll get a #NAME? error.
HTH
Jason
Atlanta, GA
-----Original Message-----
I have no idea where I've gone wrong. Given the
following
cells:
J13= Start Date & Time
K13=End Date & Time
AE66:AE83=Range of non-working holidays
H5= Start of workday (a time)
H6=End of workday (also a time)
I've evolved (with a lot of help) the followng
formula.
It is supposed to return the number of workdays and
workhours between the start date and time and the end
date and time. Much of the time it works pretty well,
but
too often I'm getting negative hour counts. I have no
idea where I've gone wrong. HELP! I'm desperate and
tired. Here's the formula:
=IF(AND(INT(J13)=INT(K13),NOT(ISNA(MATCH(INT(J1 3),
AE66:AE83,0)))),"0 days 0 hours", IF(INT(J13)=INT(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
consistent? All I need is, for a wide variety of
starting and ending dates/times, to calculate the
number
of workdays and workhours between the two.
.
.
|