Complex problem - Need help with formula or coding

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

Here's the scenario....I've developed an MS Access database that links our
HEAT Help Desk software. The Help Desk software runs reports on 24/7 hours,
but I need statistics that show only 10 hour days and if a call extends over
the weekend, the weekend needs to be excluded. Right now, I'm doing my
queries in Access and then transferring them to Excel where I use the
NETWORKDAYS function statement that looks like this:

=IF(NETWORKDAYS(B472,C472,$D$2:$D$4)-1>0,(C472-(INT(C472)+$B$2))-(B472-(INT(B472)+$B$3))+(NETWORKDAYS(B472,C472,$D$2:$D$4)-2)*$B$4,C472-B472)

It would be so great if I could do it all in Access since I'm trying to
develop a system that others can use. I'd like to eventually automate the
process. Any suggestions? I have a feeling this might stump the pros out
there. Anyone up for a challenge? :)

--Sandy
 
Here is a function that will return the number of working days between two
dates and replaces the NETWORKDAYS function in Excel. You will need a
holidays table with all your holidays entered in it. My table name is
"Holidays" (clever, what) and the date field is "holdate"

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'D Hargis
'Calculates the number of working days between two dates
'dtmStart - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function
 
WOW! You're good! I can't wait to test this. I'm really impressed! You
are a GOD! :) Klatuu, how can I contact you either by email or telephone? I
might have a followup question.

--Sandy

Klatuu said:
Here is a function that will return the number of working days between two
dates and replaces the NETWORKDAYS function in Excel. You will need a
holidays table with all your holidays entered in it. My table name is
"Holidays" (clever, what) and the date field is "holdate"

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'D Hargis
'Calculates the number of working days between two dates
'dtmStart - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function


Sandy said:
Hi all,

Here's the scenario....I've developed an MS Access database that links our
HEAT Help Desk software. The Help Desk software runs reports on 24/7 hours,
but I need statistics that show only 10 hour days and if a call extends over
the weekend, the weekend needs to be excluded. Right now, I'm doing my
queries in Access and then transferring them to Excel where I use the
NETWORKDAYS function statement that looks like this:

=IF(NETWORKDAYS(B472,C472,$D$2:$D$4)-1>0,(C472-(INT(C472)+$B$2))-(B472-(INT(B472)+$B$3))+(NETWORKDAYS(B472,C472,$D$2:$D$4)-2)*$B$4,C472-B472)

It would be so great if I could do it all in Access since I'm trying to
develop a system that others can use. I'd like to eventually automate the
process. Any suggestions? I have a feeling this might stump the pros out
there. Anyone up for a challenge? :)

--Sandy
Click to expand...
Click to expand...
 
972-946-63004
(e-mail address removed)

I certainly don't qualify for God status. I once thought someone called me
a demi-god, but it turn's out they meant "god, what a dummy"

Sandy said:
WOW! You're good! I can't wait to test this. I'm really impressed! You
are a GOD! :) Klatuu, how can I contact you either by email or telephone? I
might have a followup question.

--Sandy

Klatuu said:
Here is a function that will return the number of working days between two
dates and replaces the NETWORKDAYS function in Excel. You will need a
holidays table with all your holidays entered in it. My table name is
"Holidays" (clever, what) and the date field is "holdate"

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'D Hargis
'Calculates the number of working days between two dates
'dtmStart - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function


Sandy said:
Hi all,

Here's the scenario....I've developed an MS Access database that links our
HEAT Help Desk software. The Help Desk software runs reports on 24/7 hours,
but I need statistics that show only 10 hour days and if a call extends over
the weekend, the weekend needs to be excluded. Right now, I'm doing my
queries in Access and then transferring them to Excel where I use the
NETWORKDAYS function statement that looks like this:

=IF(NETWORKDAYS(B472,C472,$D$2:$D$4)-1>0,(C472-(INT(C472)+$B$2))-(B472-(INT(B472)+$B$3))+(NETWORKDAYS(B472,C472,$D$2:$D$4)-2)*$B$4,C472-B472)

It would be so great if I could do it all in Access since I'm trying to
develop a system that others can use. I'd like to eventually automate the
process. Any suggestions? I have a feeling this might stump the pros out
there. Anyone up for a challenge? :)

--Sandy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top