Get first Day of each month

WineSnob

Not Bright but TENACIOUS
Local time
Today, 02:32
Joined
Aug 9, 2010
Messages
211
I am trying to get the first day of each month using a date as the argument. Here is the code I am trying to get work . It is CLOSE except for the month of February returns 1/1/2013 from 2/2/2013. The other months are OK. See the resultls below the code. What am I missing to get February to return 2/1/2013?

Dim nDate As Integer
Dim MonthDays As Integer
' Return the first day in the specified month.
If dtmDate = 0 Then
' Did the caller pass in a date? If not, use
' the current date.
dtmDate = Date
End If

Dim nStartDate As Date
For nStartDate = dtmDate To dtmDate + 360

fnFirstDayInMonth = DateSerial(year(nStartDate), _
Month(nStartDate), 1)

nStartDate = nStartDate + MonthDays
MonthDays = DateSerial(year(nStartDate), Month(nStartDate) + 1, 1) - DateSerial(year(nStartDate), Month(nStartDate), 1)

Debug.Print "nStartDate " & nStartDate & "# of Days in StartDate " & MonthDays & " Value of fnFirstDayinMonth " & fnFirstDayInMonth
'Debug.Print "Value of fnFirstDayinMonth " & fnFirstDayInMonth


Next nStartDate

End Function


Here is what the code returns:
nStartDate 1/1/2013# of Days in StartDate 31 Value of fnFirstDayinMonth 1/1/2013
nStartDate 2/2/2013# of Days in StartDate 28 Value of fnFirstDayinMonth 1/1/2013
nStartDate 3/3/2013# of Days in StartDate 31 Value of fnFirstDayinMonth 2/1/2013
nStartDate 4/4/2013# of Days in StartDate 30 Value of fnFirstDayinMonth 3/1/2013
nStartDate 5/5/2013# of Days in StartDate 31 Value of fnFirstDayinMonth 4/1/2013
nStartDate 6/6/2013# of Days in StartDate 30 Value of fnFirstDayinMonth 5/1/2013
nStartDate 7/7/2013# of Days in StartDate 31 Value of fnFirstDayinMonth 6/1/2013
nStartDate 8/8/2013# of Days in StartDate 31 Value of fnFirstDayinMonth 7/1/2013
nStartDate 9/9/2013# of Days in StartDate 30 Value of fnFirstDayinMonth 8/1/2013
nStartDate 10/10/2013# of Days in StartDate 31 Value of fnFirstDayinMonth 9/1/2013
nStartDate 11/11/2013# of Days in StartDate 30 Value of fnFirstDayinMonth 10/1/2013
nStartDate 12/12/2013# of Days in StartDate 31 Value of fnFirstDayinMonth 11/1/2013
nStartDate 1/13/2014# of Days in StartDate 31 Value of fnFirstDayinMonth 12/1/2013
 
Actually, unless I've missed something, the other dates aren't ok:

nStartDate 11/11/2013# of Days in StartDate 30 Value of fnFirstDayinMonth 10/1/2013

I think this is the offending line:

nStartDate = nStartDate + MonthDays

It occurs after you've determined the first day of the month, but before you've calculated the number of days in the month. This means you're working with 2 different dates in your loop. You should move that line to just before this line:

Next nStartDate
 
Last edited:
The code logic is messed up, so the debug.print does not print values that belong together. The function itself is fine.
 

Users who are viewing this thread

Back
Top Bottom