Date Calculation for Report

[Stockwell43]

Hello,

I have a database that I am trying to calculate the time (in days minus
weekends and holidays) between the Original Date and the current date.

I'm not sure if this needs to be placed on the form, query or add a couple
of fields to the report but this is what I am trying to accomplish.

I have an "Original Date" field on the form. We have 10 days from that date
to payoff the loan and send out the title to the customer. When the manager
pulls a report, he wants to see the Original Date and the number of days that
have passed to see if he is within his 10 day window (again minus weekends
and holidays). I suppose I can place a checkbox on the form to show if the 10
days was met so as days are elapsing over day the checkbox will show it was
completed(he wants all the records to show for the month whether it's
completed or not because he is looking at other data on this report as well.

If this is possible without to much complication, any help would be most
appreciated! At best, if I can exclude weekdays I can place a checkbox to
show a holiday falls in the date range and he can subtract one day from what
is shown.

Thanks!!!

 

[Al Campagna]

Stockwell,
I'm a bit unclear as to what you're trying to do... but...
On my website (below), I have a sample 97 and 2003 file in "Access Tips"
that calculates the number of work days between two dates (Calculate Number
of Workdays). It ignores any weekend days, and holidays in the
calculation.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Stockwell43]

Hi Al, thank you for responding.

This looks like it could work. Very nice piece! Is the calendar updated
automatically every year or does a table have to be updated? If automatic
then a really very nice piece!!

I am having an issue though. It appears the number of actual days works fine
but the work days is adding one extra day.

In other words, from 7/15/2008 - 7/21/2008 I get 6 total days but for Work
days I get 5 and should be 4 no?

Thanks!!

 

[Klatuu]

The calendar has to be updated. There is no way to autmatically know what
everybody's holidays are.
the U.S. doesn't celibrate Boxing Day, Canada doesn't have an MLK day, and I
seriously doubt the UK recognizes July 4th <g>

As to the number of days, it realy is 5.
The work days are: 15, 16, 17, 18, and 21

My work days function does the calculation the same way:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

 

[Stockwell43]

Hi Klatuu,

If the start day is included in the workdays, why is it not included in the
total days? because that number should be seven correct?

What I was referring to by Auto update of the calendar was if it was somehow
set to recognize U.S. holidays. When I opened the form it was updated with
2008 and wasn't sure if Al did that or it updated when I opened it.

I know that sound a bit dense but I'm still amazed at what you guys can do
with Access so I guess it was wishful thinking )

The calendar has to be updated. There is no way to autmatically know what
everybody's holidays are.
the U.S. doesn't celibrate Boxing Day, Canada doesn't have an MLK day, and I
seriously doubt the UK recognizes July 4th <g>

As to the number of days, it realy is 5.
The work days are: 15, 16, 17, 18, and 21

My work days function does the calculation the same way:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function


--
Dave Hargis, Microsoft Access MVP

Hi Al, thank you for responding.

This looks like it could work. Very nice piece! Is the calendar updated
automatically every year or does a table have to be updated? If automatic
then a really very nice piece!!

I am having an issue though. It appears the number of actual days works fine
but the work days is adding one extra day.

In other words, from 7/15/2008 - 7/21/2008 I get 6 total days but for Work
days I get 5 and should be 4 no?

Thanks!!

 

[Stockwell43]

Klatuu, how does yours work? I guess I would place the code in a Module
right? What do I name the Module? My start date is OriginalDate does that
need to be changed so your code picks it up? I'm sure I need to place and end
date field but what about a field to show the difference in days?

In other words, when the user enters the "OriginalDate", when they complete
thier process they would enter a "CompleteDate" once the complete date is
entered, I want another textbox to show the number of days that elapsed not
including weekends and holidays. Does that make sense?

The calendar has to be updated. There is no way to autmatically know what
everybody's holidays are.
the U.S. doesn't celibrate Boxing Day, Canada doesn't have an MLK day, and I
seriously doubt the UK recognizes July 4th <g>

As to the number of days, it realy is 5.
The work days are: 15, 16, 17, 18, and 21

My work days function does the calculation the same way:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function


--
Dave Hargis, Microsoft Access MVP

Hi Al, thank you for responding.

This looks like it could work. Very nice piece! Is the calendar updated
automatically every year or does a table have to be updated? If automatic
then a really very nice piece!!

I am having an issue though. It appears the number of actual days works fine
but the work days is adding one extra day.

In other words, from 7/15/2008 - 7/21/2008 I get 6 total days but for Work
days I get 5 and should be 4 no?

Thanks!!

 

[Al Campagna]

Stockwell43,
Yes... the calendar just needs an update once a year. If July 4th falls
on a Tuesday, usually Monday will be a holiday too. If it falls on
Thursday, Friday could be a holiday.
Other holidays fall on (ex.) "2nd Tuesday of November"... which would be
different for each year.

If you don't agree with my holiday count, just put in a -1 in the
code... to yield the number you want.
But... I'm pretty sure my count is OK... that's your call.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Stockwell43]

Thanks Al!

I'm sure your dates are fine. My only question was why does it one begin on
the start and the other begin the day after the start date. I just thought
they should both start calculating on the same day. No Biggie, it's pretty
cool though!


I think I am going to try this in a new database I am working on along with
some of your other tips from your website. Nice site and good stuff, I passed
it along to some others who use Access as well.

Thank you for all your help!!!!

 

[Klatuu]

The code would go in a standard module.
Name it anything you like except the name of the function.
I have mine in a module named modDateFunctions. It is a collection of
various date manipulation routines.

You do not need to change the code based on what values you are using in
your application. The normal call to a function is all you need:

=CalcWorkDays(StartDate, EndDate)

Or as you are asking, on a form, you can do the same thing with an unbound
calcualted control.
If you are going to use it in a report, you probably will want to put a text
box on the report and use the start and end date field names from the
report's record source. You put the calculation in the text box control
source:

--
Dave Hargis, Microsoft Access MVP

Klatuu, how does yours work? I guess I would place the code in a Module
right? What do I name the Module? My start date is OriginalDate does that
need to be changed so your code picks it up? I'm sure I need to place and end
date field but what about a field to show the difference in days?

In other words, when the user enters the "OriginalDate", when they complete
thier process they would enter a "CompleteDate" once the complete date is
entered, I want another textbox to show the number of days that elapsed not
including weekends and holidays. Does that make sense?

The calendar has to be updated. There is no way to autmatically know what
everybody's holidays are.
the U.S. doesn't celibrate Boxing Day, Canada doesn't have an MLK day, and I
seriously doubt the UK recognizes July 4th <g>

As to the number of days, it realy is 5.
The work days are: 15, 16, 17, 18, and 21

My work days function does the calculation the same way:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function


--
Dave Hargis, Microsoft Access MVP

Hi Al, thank you for responding.

This looks like it could work. Very nice piece! Is the calendar updated
automatically every year or does a table have to be updated? If automatic
then a really very nice piece!!

I am having an issue though. It appears the number of actual days works fine
but the work days is adding one extra day.

In other words, from 7/15/2008 - 7/21/2008 I get 6 total days but for Work
days I get 5 and should be 4 no?

Thanks!!

:

Stockwell,
I'm a bit unclear as to what you're trying to do... but...
On my website (below), I have a sample 97 and 2003 file in "Access Tips"
that calculates the number of work days between two dates (Calculate Number
of Workdays). It ignores any weekend days, and holidays in the
calculation.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Hello,

I have a database that I am trying to calculate the time (in days minus
weekends and holidays) between the Original Date and the current date.

I'm not sure if this needs to be placed on the form, query or add a couple
of fields to the report but this is what I am trying to accomplish.

I have an "Original Date" field on the form. We have 10 days from that
date
to payoff the loan and send out the title to the customer. When the
manager
pulls a report, he wants to see the Original Date and the number of days
that
have passed to see if he is within his 10 day window (again minus weekends
and holidays). I suppose I can place a checkbox on the form to show if the
10
days was met so as days are elapsing over day the checkbox will show it
was
completed(he wants all the records to show for the month whether it's
completed or not because he is looking at other data on this report as
well.

If this is possible without to much complication, any help would be most
appreciated! At best, if I can exclude weekdays I can place a checkbox to
show a holiday falls in the date range and he can subtract one day from
what
is shown.

Thanks!!!

 

[Klatuu]

Al,
The OP does have a reasonable question.
You calculation seems to be different when counting total days from counting
working days.
I agree that the count of working days is 5.
But, then the total days should be 7

15, 16, 17, 18, 19, 20, 21

You are using a straight DateDiff, which is the number of days between the
two, but that is not inclusive of the last day.

 

[Al Campagna]

Klatuu,
Well, I think the problem may be a question of semantics.
"Difference" vs. "Work Days"
The "difference" between 5th and 9th is 4... even though...
5,6,7,8,9
Otherwise... DateDiff wouldn't be true.

But... when we say we worked from the 5th to the 9th,
that would (as you stated) be 5 days, not 4.
So... since I was trying to show just that... I'll need to change that
calculation. (DateDiff +1)
--
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Klatuu]

I fully understand, Al. I guess my perception is prejudiced because I worked
in banking in the '80s on ATM systems. Date handling is very important in
that world.
But back in the day we didn't have no fancy DateDiff and DateAdd or non of
them other store bought routines. We were real men and had to roll our own
and walk uphill in the snow to and from work <g>

 

[Matt]

Would there also be a way to include time in the CalcWorkDays function, so
instead of two dates (7/1/2008 and 7/7/2008), it could calculate the
difference between two general dates excluding holidays and weekends
(7/1/2008 7:05AM and 7/7/2008 2:00PM)?

Right now I am using the Diff2Dates functionas
referenced(http://www.accessmvp.com/djsteele/Diff2Dates.html) , but this
calculation does not remove weekends or holidays.

 

[Klatuu]

No, the calculations are all done in days.
Doug's routine is more precise in time. You might modify his code to
subtract holidays or use the time of day to determine the "business day".

For example, in banking, our cut-off time was between 2:00 PM and 3:00 PM
(depending on the bank), so if the time is after that, we would add one day
to the date, this check to see if it was a weekend or holiday, etc.

 

[Matt]

OK, thanks...I'll work on that, but I have been having problems altering the
code. I could get it to get the appropriate number of days but then the
hours and minutes would not calculate properly...any suggestions on coding
would be welcome!

 

[Klatuu]

Well, you can always change a calculation that uses a DateDiff function to
return any available time value. The only difference is you would than have
to convert each non workday from a day to the number of time units you are
using in your calculation.

The issue is you must break everything down to the common demoninator. In
your case, it would be minutes.

 

[Matt]

Could you provide an example. I think I understand what you are saying i
need to do, but an example of code would definitely help with my project.

Thanks

 

[Michael Gramelspacher]

Could you provide an example. I think I understand what you are saying i
need to do, but an example of code would definitely help with my project.

Thanks

Here is something I just did for fun. It is supposed to calculate work hours
from a date+time until another date+time. It assumes both dates are work dates.

The workdays function I use to get the difference uses a calendar table. (see:
http://www.psci.net/gramelsp/temp/Calendar.zip) It does not include the end
date. Because of that I added one day. If your workday function includes the
end date, you do not add the extra day.

I have to caution that this may not work, but at least it is a starting point.

Function WorkHoursBetween(start_date As Date, _
end_date As Date, _
Optional start_time As Date, _
Optional end_time As Date) As Long

' start_date is the beginning date time value
' end_date is the ending date time value
' start_time is the time of day the work day begins
' end_time is the time of day the work day ends

' assumption is that start_date and end_date are workdays

' Returns work hours: (includes lunch time)
' ?WorkHoursBetween(#7/28/2008 14:30:00#,#8/4/2008 11:00:00#)
' 37

' ?WorkHoursBetween(#7/28/2008 14:30:00#,#8/4/2008 11:00:00#,
' #07:00:00#,#17:00:00#)
' 47

If Not isWorkday(start_date) Then
WorkHoursBetween = 0
Exit Function
End If

If Not isWorkday(end_date) Then
WorkHoursBetween = 0
Exit Function
End If

If end_date < start_date Then
WorkHoursBetween = 0
Exit Function
End If

If start_time = #12:00:00 AM# Then start_time = #8:00:00 AM#
If end_time = #12:00:00 AM# Then end_time = #4:00:00 PM#


'Debug.Print IIf(TimeValue(start_date) < start_time, start_time, _
' TimeValue(start_date))
'Debug.Print IIf(TimeValue(end_date) < start_time, end_time, _
'TimeValue(end_date))


WorkHoursBetween = (WorkdaysBetween(DateAdd("d", _
DateDiff("d", 0, start_date), 0), _
DateAdd("d", DateDiff("d", 0, end_date), 0)) + 1) * _
DateDiff("h", start_time, end_time) - _
((DateDiff("h", start_time, _
IIf(TimeValue(start_date) < start_time, start_time, _
TimeValue(start_date))) + (DateDiff("h", _
IIf(TimeValue(end_date) < start_time, end_time, _
TimeValue(end_date)), end_time))))

End Function

 

[Matt]

This code didnt work right away, but I will work on it and let you know if I
find a solution. Thank Michael!

 

[Michael Gramelspacher]

This code didnt work right away, but I will work on it and let you know if I
find a solution. Thank Michael!

"Michael Gramelspacher" wrote:

Probably the only way you will get anywhere with this is to download the
calendar.zip file and try the function there.

Test it in the Visual Basic code window by copying this to the immediate window
and pressing enter at the end of the line.

WorkHoursBetween(#7/28/2008 14:30:00#,#8/4/2008 11:00:00#)

but rather than do that, try this using the function you were given at the top
of this thread:

Function WorkHoursBetween2(start_date As Date, _
end_date As Date, _
Optional start_time As Date, _
Optional end_time As Date) As Long

' start_date is the beginning date time value
' end_date is the ending date time value
' start_time is the time of day the work day begins
' end_time is the time of day the work day ends

' assumption is that start_date and end_date are workdays

' Returns work hours: (includes lunch time)
' ?WorkHoursBetween(#7/28/2008 14:30:00#,#8/3/2008 11:00:00#)
' 29

' ?WorkHoursBetween(#7/28/2008 14:30:00#,#8/3/2008 11:00:00#,
' #07:00:00#,#17:00:00#)
' 37

If start_time = #12:00:00 AM# Then start_time = #8:00:00 AM#
If end_time = #12:00:00 AM# Then end_time = #4:00:00 PM#


WorkHoursBetween2 = CalcWorkDays(DateValue(start_date), _
DateValue(end_date)) * _
DateDiff("h", start_time, end_time) - _
((DateDiff("h", start_time, _
IIf(TimeValue(start_date) < start_time, start_time, _
TimeValue(start_date))) + _
(DateDiff("h", _
IIf(TimeValue(end_date) < start_time, end_time, _
TimeValue(end_date)), end_time))))

End Function