Comparing 2 diff Dates in VBA

[jfarc]

My brain is fried on this one - I can't get through this 'simple' logic.

I have (2) dates:
aDate
bDate - this will always be equal to or up to 4 or 5 days > (after) aDate

Desired logic:
If the Month of aDate = Month of bDate, then leave both dates as they are.
If the Month of bDate is > the Month of aDate, then keep aDate the same and make bDate = the last day of the month of aDate.

Sample input & results:

Sample (A) Input
aDate = 09/27/01
bDate = 09/29/01

Sample (A) result
aDate = 09/27/01
bDate = 09/29/01

Sample (B) Input
aDate = 09/27/01
bDate = 10/02/01

Sample (B) result
aDate = 09/27/01
bDate = 09/30/01

These Dates will not be resident in an Excel worksheet, so I need this code in VBA form. Thank you!

 

[LxQ]

hm.. let me try my hand at VBA.. anyway, the logic should be that if the month of bDate is greater than the month of aDate, then take the date of bDate, make it the first of the month, then subtract 1 to make it the last day of the previous month.

Public Function ChangeDate

Dim aDate As Date
Dim bDate As Date

If Month(bDate)>Month(aDate) Then
bDate=DateSerial(Year(bDate), Month(bdate), Day(1))-1
End If

End Function

 

[AlphaFrog]

One small change to LxQ's code. If aDate is in December and bDate is in January, then it wouldn't work. Give this a try.

<font face=Courier New><SPAN style="color:#00007F">If</SPAN> Month(bDate) <> Month(aDate) <SPAN style="color:#00007F">Then</SPAN><br>    bDate = DateSerial(Year(bDate), Month(bDate), 0)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN></FONT>

 

[jfarc]

Thanks to both of you!

Yes, I saw the Dec/Jan issue and the ', Day(1)' issue I was having was corrected with the ', 0' replacement.

It's exactly what I was looking for.