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(J13),
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.

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(J13) ,
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.
.

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(J13 ),
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.
.

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.
.


.

Hi, Jason--

Thnaks for ytour help! I'm going to take yours and Myrna's suggestions =
and=20
see if I can get my sheet to work. Sorry it took so long to reply, but =
I'd=20
reached my frustration point with the thing and just went to bed. I'll =
let you=20
know how this whole thing plays out when I get into it later today!
-----Original Message-----
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(INT (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.
.

.

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.
.


.

Just an additional comment on the VBA code below. It is FASTER than a version
that uses NETWORKDAYS (instead of my IsWorkday function) for intervals up to
about 30 workdays. For longer time spans, the NETWORKDAYS version is faster
but of course it requires that the ATP be installed and a VBA reference
created to it.

On Thu, 17 Feb 2005 22:34:15 -0600, Myrna Larson
wrote:

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.
.