Save me from insanity???

[Sue]

Work has been quite stressful. I've decided to leave my current position. My
last workday will be July 3. I work 4 days a week. I have a command button
that pops up a little form that gives the number of days remaining until July
3rd. I also want to indicate the number of WORKDAYS remaining (assuming I
take no vacation, holiday, or sick time between now and then). The
calculation I have for that is:

=((DateDiff("d",Date(),#7/3/2008#))/7)*4

I suspect that this won't always be accurate, however, because the
calculation I'm using doesn't distinguish between workdays and weekends.

You have NO idea how much my stress level decreases when I click that
command button. Can you help me to ensure that my calculation is really
accurate, though?

Many many thanks!!!

 

[raskew via AccessMonster.com]

Sue -

Hang in there. Only 124 workdays (today's 11-Jan-2008) till you're free.

? findworkdays(date(), #7/3/2008#)
124

Function FindWorkDays(varDate1 As Variant, varDate2 As Variant, Optional incl
As Boolean) As Double

'*******************************************
'Purpose: returns number of workdays between two dates
'Coded by: raskew
'Inputs: from debug (immediate) window
' ? findworkdays(#4/01/2006#, #02/26/2007#)
'Outputs: 236
'*******************************************
Dim dteDate1 As Date
Dim dteDate2 As Date
Dim fdays As Integer
Dim fweeks As Integer
Dim ldays As Integer
Dim intdays As Integer
Dim x As Integer

incl = IIf(IsMissing(incl), True, incl)

If Not IsNull(varDate1) And Not IsNull(varDate2) Then
If IsDate(varDate1) And IsDate(varDate2) Then
dteDate1 = DateValue(varDate1)
dteDate2 = DateValue(varDate2)
intdays = DateDiff("d", dteDate1, dteDate2)
x = WeekDay(dteDate1, 1)

fdays = 7 - WeekDay(dteDate1, 1) '# of weekdays in starting week
fweeks = 5 * Int((intdays - fdays) / 7) '# of calendar days in full
weeks
ldays = WeekDay(dteDate2, 1) - 1 '#of weekdays in ending week
FindWorkDays = fdays + fweeks + ldays + IIf(incl, 0, IIf(IsWeekday
(dteDate1), -1, 0))
End If
End If

End Function

HTH - Bob

 

[raskew via AccessMonster.com]

Oops:

I work 4 days a week.

Missed that! Since you're working only 80% of what most of us are, perhaps
you can spend that extra day modifying the code to meet your horrendous
situation.

Bob

Sue -

Hang in there. Only 124 workdays (today's 11-Jan-2008) till you're free.

? findworkdays(date(), #7/3/2008#)
124

Function FindWorkDays(varDate1 As Variant, varDate2 As Variant, Optional incl
As Boolean) As Double

'*******************************************
'Purpose: returns number of workdays between two dates
'Coded by: raskew
'Inputs: from debug (immediate) window
' ? findworkdays(#4/01/2006#, #02/26/2007#)
'Outputs: 236
'*******************************************
Dim dteDate1 As Date
Dim dteDate2 As Date
Dim fdays As Integer
Dim fweeks As Integer
Dim ldays As Integer
Dim intdays As Integer
Dim x As Integer

incl = IIf(IsMissing(incl), True, incl)

If Not IsNull(varDate1) And Not IsNull(varDate2) Then
If IsDate(varDate1) And IsDate(varDate2) Then
dteDate1 = DateValue(varDate1)
dteDate2 = DateValue(varDate2)
intdays = DateDiff("d", dteDate1, dteDate2)
x = WeekDay(dteDate1, 1)

fdays = 7 - WeekDay(dteDate1, 1) '# of weekdays in starting week
fweeks = 5 * Int((intdays - fdays) / 7) '# of calendar days in full
weeks
ldays = WeekDay(dteDate2, 1) - 1 '#of weekdays in ending week
FindWorkDays = fdays + fweeks + ldays + IIf(incl, 0, IIf(IsWeekday
(dteDate1), -1, 0))
End If
End If

End Function

HTH - Bob

Work has been quite stressful. I've decided to leave my current position. My
last workday will be July 3. I work 4 days a week. I have a command button

[quoted text clipped - 13 lines]

Many many thanks!!!

 

[Gary Walter]

Work has been quite stressful. I've decided to leave my current position.
My
last workday will be July 3. I work 4 days a week. I have a command button
that pops up a little form that gives the number of days remaining until
July
3rd. I also want to indicate the number of WORKDAYS remaining (assuming I
take no vacation, holiday, or sick time between now and then). The
calculation I have for that is:

=((DateDiff("d",Date(),#7/3/2008#))/7)*4

I suspect that this won't always be accurate, however, because the
calculation I'm using doesn't distinguish between workdays and weekends.

You have NO idea how much my stress level decreases when I click that
command button. Can you help me to ensure that my calculation is really
accurate, though?

Hi Sue,

I think the simplest solution is to create a Calendar table
of every date. Many simple ways to do this...

I'd just start some dates in a column in Excel, use Excel's power
to "drag" sequential dates "down" until you get to July 3.

Import into Access table (say "tblCal" -- as say field "Dte")


Add 3 Yes/No fields (default value = 0) for Holiday, Vacation, WorkDay
to your table.

Change appropriate Holiday or Vacation days to True (-1).

Run update query to set appropriate WorkDay's to True

Say you only work Mon(2), Tues(3), Thu(5), Fri(6)
(some systems may define WeekDay's differently)

UPDATE tblCal
SET [WorkDay] = -1
WHERE
WeekDay(Dte) IN (2, 3, 5, 6)
AND
[Holiday] = 0
AND
[Vacation] = 0;

Review tblCal that WorkDay values are correct.

Query to count workdays from today until July 3, 2008:

SELECT
Abs(Sum([WorkDay]) AS WorkDaysLeft
FROM
tblCal
WHERE
[Dte] BETWEEN Date() AND #7/3/2008#;

or, a textbox ControlSource might use domain function

= (-1) * DSUM("WorkDay", "tblCal", "[Dte] BETWEEN Date() AND #7/3/2008#")

good luck,

gary