Manipulating Dates

[Guest]

I could use some help with manipulating existing dates around weekends and
holidays. In other words, I have existing dates that need to be changed so
that they only fall on a workday. I need to write a routine that will do the
following:

1) Evaluate an existing date in a table field;
2) If that date is a weekend or holiday, change the date to the next
available workday date (not a weekend or holiday).

I already have 3 tables that I believe are all that may be needed:

1) one that contains all dates through 2040 [2000-2040];
2) one that lists out the known federal holidays [ref_FederalHolidays];
3) one that only lists workdays [tbl_WorkdaysOnly]. This table includes a
field that has a consecutive incremental number that I already use for
calculating elapsed workdays.

I am in the federal employment arena, thus I created this scheme so that
when our esteemed leaders 'declare' an otherwise normal workday as a holiday,
I can recreate the WorkdaysOnly table on the fly.

At this point, I only need this as a DBA tool for cleaning up existing data
prior to a migration. I appreciate any help you can provide.

 

[RuralGuy]

I could use some help with manipulating existing dates around weekends and
holidays. In other words, I have existing dates that need to be changed so
that they only fall on a workday. I need to write a routine that will do the
following:

1) Evaluate an existing date in a table field;
2) If that date is a weekend or holiday, change the date to the next
available workday date (not a weekend or holiday).

I already have 3 tables that I believe are all that may be needed:

1) one that contains all dates through 2040 [2000-2040];
2) one that lists out the known federal holidays [ref_FederalHolidays];
3) one that only lists workdays [tbl_WorkdaysOnly]. This table includes a
field that has a consecutive incremental number that I already use for
calculating elapsed workdays.

I am in the federal employment arena, thus I created this scheme so that
when our esteemed leaders 'declare' an otherwise normal workday as a holiday,
I can recreate the WorkdaysOnly table on the fly.

At this point, I only need this as a DBA tool for cleaning up existing data
prior to a migration. I appreciate any help you can provide.

Here's a link to some Date functions:
http://www.pacificdb.com.au/MVP/Code/Dates.htm
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[RD]

I could use some help with manipulating existing dates around weekends and
holidays. In other words, I have existing dates that need to be changed so
that they only fall on a workday. I need to write a routine that will do the
following:

1) Evaluate an existing date in a table field;
2) If that date is a weekend or holiday, change the date to the next
available workday date (not a weekend or holiday).

I already have 3 tables that I believe are all that may be needed:

1) one that contains all dates through 2040 [2000-2040];
2) one that lists out the known federal holidays [ref_FederalHolidays];
3) one that only lists workdays [tbl_WorkdaysOnly]. This table includes a
field that has a consecutive incremental number that I already use for
calculating elapsed workdays.

I am in the federal employment arena, thus I created this scheme so that
when our esteemed leaders 'declare' an otherwise normal workday as a holiday,
I can recreate the WorkdaysOnly table on the fly.

At this point, I only need this as a DBA tool for cleaning up existing data
prior to a migration. I appreciate any help you can provide.

Ah, the "floating holiday" thing. It was new to me when I came to work for the
gummint. Strange concept. Let's see ... it's a holiday that occurs on a normal
work day that you don't take off but you get paid vacation pay for. You *can*
take it off but that's sort of frowned upon. Nope ... it's a holiday you spend
at work ... getting paid. Here comes that headache, again.

There are some good date manipulation routines here:
http://www.mvps.org/access/datetime/index.html

HTH,
RD

 

[Guest]

You only need one of those three tables. It is the one with the holidays in
it. The other two are unncesssary and only take up space.
Here is a pair of functions that will do what you need. The first
determines if the data past to it is a working day or not. In this function,
you will need to change the name of the table and the field where a holiday
date is found. The second function is the one you will call. It will find
the next working day from the date entered.

Public Function IsWorkDay(dtmSomeDay As Date) As Boolean
Dim blnWorkingDay
blnWorkingDay = Weekday(dtmSomeDay, vbMonday) < 6
If blnWorkingDay Then
blnWorkingDay = IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmSomeDay & "#"))
End If
IsWorkDay = blnWorkingDay
End Function

Public Function NextWorkDay(dtmSomeDay As Date)
Dim dtmDayOff As Date
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Loop
NextWorkDay = dtmSomeDay
End Function