retrieve a date

[Kersey]

I want to get the date of the first monday in this quarter,

how should i do this ???

[Paul Warren]

I haven't got time to write the code but the gist would be :

1. get the current month as a number
2. Divide by 3 and the integer tells you the quarter number
0 = 1st
1 = 2nd
2 = 3rd
3 = 4th

3. Multiply the quarter by 3 to get the first Month in that quarter
4. Get the day of the 1st of the month given in step 3
5. add the number of days between the one you've got and a Monday i.e. If you get a Wednesday add 5 days to the date.

Sorry there's no code but it's pretty straight forward stuff. You can also call an Excel function which returns such things but I haven't really got time to work that one out right now. Have a good weekend.

I'd imagine that you know what the first and last dates of the quarter are. You could create a function like this:

Public Function GetFirstMonday(StartDate As Date, EndDate As Date) As Date
  Dim dteIndex As Date

  For dteIndex = StartDate To EndDate
    If WeekDay(dteIndex) = vbMonday Then
      GetFirstMonday = dteIndex
      Exit For
    End If
  Next dteIndex

End Function

...and pass those dates in. For example:

Dim dteFirstMonday As Date

dteFirstMonday = GetFirstMonday(#1/1/00#, #3/31/00#)

MsgBox dteFirstMonday

Paul

PS - Paul Warren and I posted at roughly the same time...I'm gonna put some of his logic into my function to make it more user friendly!

[Edited by PWNettle on 11-17-2000 at 10:55 AM]

After reviewing Paul Warren's post I changed my function to this:

Public Function GetFirstMonday() As Date
  Dim intQuarter As Integer
  Dim dteStart As Date
  Dim dteIndex As Date
  
  '  Determine the current quarter.
  intQuarter = Month(Date) \ 3
  '  Build a start date for the loop (First day of the first month of the quarter).
  dteStart = CDate(intQuarter * 3 + 1 & "/1/" & Year(Date))
  
  '  Loop thru the first week of the first month of the quarter looking for the first Monday.
  For dteIndex = dteStart To (dteStart + 7)
    If Weekday(dteIndex) = vbMonday Then
      GetFirstMonday = dteIndex
      Exit For
    End If
  Next dteIndex
  
End Function

Which would be used like this:

MsgBox "The first day of the current quarter is:  " & GetFirstMonday()

I had to deviate slightly from Paul Warren's logic though. To get the first month of the quarter I had to multiply by 3 and add 1...otherwise you could get a first month of zero if you just multiplied by 3 on 'quarter 0'. Thanks to Paul Warren for the good ideas.

Cheers,
Paul


[Edited by PWNettle on 11-17-2000 at 11:09 AM]