calculate based on first monday in month 1

[haytatreides]

I have a form that generates a report dynamically, but i need it to calculate from the first tuesday after the first monday in a month to the next month's first monday (hope that makes sense). How can I do this? any ideas?

hayt

 

[BobJacksonNCI]

hayt,
I wish I had the time to work through this and make sure it's correct. I'm sure one of the resident Gurus will be able to help and/or provide a slicker solution.
To get you started, look at this:

Dim n As Long
Dim workdate As Date
Dim dt1stTue As Date 'CUR MONTH 1ST TUE
Dim dt1stMon As Date 'NEXT MONTH 1ST MON

'CALCULATE THE FIRST OF THE CURRENT MONTH
workdate=DateSerial(Year(Date()), Month(Date()), 1)

For n = 0 To 6 'LOOP MAXIMUM OF 7 DAYS
workdate = DateAdd("d", n, Date())
If WeekDay(workdate) = 2 then 'MONDAY?
dt1stTue = workdate + 1 'NEXT DAY = TUESDAY
End If
Next

'CALCULATE THE FIRST OF THE NEXT MONTH
workdate=DateSerial(Year(Date()), Month(Date()) + 1, 1)

For n = 0 to 6 'LOOP MAXIMUM OF 7 DAYS
workdate = DateAdd("d", n, workdate)
If WeekDay(workdate) = 2 then 'MONDAY?
dt1stMon = workdate
end if
Next


HTH,
Bob
Your mileage may vary, but following the guidelines in faq181-2886 will help you reach your goal.

 

[SgtJarrow]

While Bob's response looks correct, are you sure that is what you want?? Apparently you are reporting for a period of time.....but based on your requirements, there will be missing information....

If the first day of a month is Tuesday, based on the code provided and your request, you will actually end up with the second Tuesday of the month. You have just left out approximately a weeks worth of data in your report....

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[MichaelRed]

Public Function basTuesAftrMon(dtIn As Date, DOW As Integer) As Date

    'Michael Red    12/4/03 Tek-tips thread702-721983 for haytatreides

    'Borrows from previous development dtNthDOW Returns the Nth occurance _
     of a Day of the week within a month

    Dim Mydt As Date

    Mydt = dtNthDOW(dtIn, 1, vbMonday)

    While Weekday(Mydt) <> DOW
        Mydt = Mydt + 1
    Wend
    
    basTuesAftrMon = Mydt


End Function
[\code]

[code]
Public Function dtNthDOW(dtDtIn As Date, intNthWkDay, Optional intDOW As Variant) As Date

    'Michael Red    11/8/2003   Return the Date of the Day of the Week [intDOW] _
     following the Nth Day of the Month _
     in the Month of the input date.  Minimal error checking, Returns Sone _
     date in the year of 1899 if the Nth Day of the Week does not fall in _
     the same month as the date in.

    'Examples of normal returns
    '? dtNthDOW(date, 1, vbMonday)
    '12/3/03
    'dtNthDOW(#11/12/03#, 1, vbTuesday)
    '11/4/03
    
    'Example of an error return
    '? dtNthDOW(DAte, 6, vbsunday)
    '12/29/1899


    Dim dtFstOfMnth As Date

    If (IsMissing(intDOW)) Then
        intDOW = vbFriday
    End If

    dtFstOfMnth = DateSerial(Year(dtDtIn), Month(dtDtIn), 1)
    While Weekday(dtFstOfMnth) <> intDOW
        dtFstOfMnth = dtFstOfMnth + 1
    Wend

    dtNthDOW = DateAdd(&quot;d&quot;, (intNthWkDay - 1) * 7, dtFstOfMnth)
    If (Month(dtDtIn) <> Month(dtNthDOW)) Then
        dtNthDOW = -1
    End If

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places