I am trying to write a macro that takes a certain date from a cell, adds 1
quarter to the month and return the last day of that month.
For example, if the date in the cell is 9/30/2005, I want it to return
12/31/2005.
Most things I tried gave me 12/30/2005.

Thanks.

How about something like:

Option Explicit
Sub testme()
Dim myDateIn As Date
Dim myDateOut As Date

myDateIn = Worksheets("sheet1").Range("a1").Value
myDateOut = DateSerial(Year(myDateIn), Month(myDateIn) + 4, 0)
MsgBox myDateOut
End Sub

We add an extra month (4 instead of 3), but use the zeroeth day of that extra
month--which excel treats as the last day of the previous month.



gottahavit wrote:

I am trying to write a macro that takes a certain date from a cell, adds 1
quarter to the month and return the last day of that month.
For example, if the date in the cell is 9/30/2005, I want it to return
12/31/2005.
Most things I tried gave me 12/30/2005.

Thanks.

--

Dave Peterson

in your example, this will work, but you don't mention whether the date
you're starting with will be a quarter ending date or not
if 9/30/2005 is in a1:
=eomonth(A1,3)

if you use other dates, post back
--


Gary


"gottahavit" wrote in message
...
I am trying to write a macro that takes a certain date from a cell, adds 1
quarter to the month and return the last day of that month.
For example, if the date in the cell is 9/30/2005, I want it to return
12/31/2005.
Most things I tried gave me 12/30/2005.

Thanks.

Function EndOfQuarter(D As Date) As Date
'returns the next quarter-end date AFTER the supplied date
'Assumes quarters end 3/31, 6/30, 9/30, and 12/31

Dim D2 As Date
Dim M As Long
Dim X As Long
Dim Y As Long

Y = Year(D)
M = Month(D)

X = M Mod 3
If X Then X = 3 - X
M = M + X + 1

Do
D2 = DateSerial(Y, M, 0)
M = M + 3
Loop While D2 <= D
EndOfQuarter = D2
End Function


On Mon, 12 Sep 2005 14:29:30 -0400, "Gary Keramidas"
wrote:

in your example, this will work, but you don't mention whether the date
you're starting with will be a quarter ending date or not
if 9/30/2005 is in a1:
=eomonth(A1,3)

if you use other dates, post back

Here's an expanded version that let's you specify when your quarters end. You
can supply any quarter-ending date; it doesn't have to be in the current year
or in the future. If you omit that date, the defaults are 3/31, 6/30, 9/30,
and 12/31. Quarters can also end in the middle of a month. But be careful
about specifying something like May 30, as there is no Feb 30, and the macro
doesn't check for that.

Option Explicit

Function EOQuarter(Dt As Date, Optional QuarterEnd As Date = #1/1/100#)
Dim D As Long
Dim M As Long
Dim Mo As Long
Dim MoQ As Long
Dim TempDt As Date
Dim Y As Long

If QuarterEnd = #1/1/100# Then QuarterEnd = #12/31/2005#
MoQ = Month(QuarterEnd)
D = Day(QuarterEnd)

Y = Year(Dt)
Mo = Month(Dt)

'M = number of months to add to current month
M = (MoQ Mod 3) - (Mo Mod 3)
If M < 0 Then M = M + 3

'if quarter ends on last day of month,
'use day 0 of following month
If Month(QuarterEnd + 1) < MoQ Then
M = M + 1
D = 0
End If

TempDt = DateSerial(Y, Mo + M, D)
If TempDt <= Dt Then TempDt = DateSerial(Y, Mo + M + 3, D)

EOQuarter = TempDt
End Function

just a question:
how do you get this to work? i wanted to test it but nothing happens when i
make an entry
--


Gary


"Myrna Larson" wrote in message
...
Here's an expanded version that let's you specify when your quarters end.
You
can supply any quarter-ending date; it doesn't have to be in the current
year
or in the future. If you omit that date, the defaults are 3/31, 6/30,
9/30,
and 12/31. Quarters can also end in the middle of a month. But be careful
about specifying something like May 30, as there is no Feb 30, and the
macro
doesn't check for that.

Option Explicit

Function EOQuarter(Dt As Date, Optional QuarterEnd As Date = #1/1/100#)
Dim D As Long
Dim M As Long
Dim Mo As Long
Dim MoQ As Long
Dim TempDt As Date
Dim Y As Long

If QuarterEnd = #1/1/100# Then QuarterEnd = #12/31/2005#
MoQ = Month(QuarterEnd)
D = Day(QuarterEnd)

Y = Year(Dt)
Mo = Month(Dt)

'M = number of months to add to current month
M = (MoQ Mod 3) - (Mo Mod 3)
If M < 0 Then M = M + 3

'if quarter ends on last day of month,
'use day 0 of following month
If Month(QuarterEnd + 1) < MoQ Then
M = M + 1
D = 0
End If

TempDt = DateSerial(Y, Mo + M, D)
If TempDt <= Dt Then TempDt = DateSerial(Y, Mo + M + 3, D)

EOQuarter = TempDt
End Function

Go to the VB Editor, select your workbook in the project pane at the upper
left, and insert a Module into your workbook. Then paste the code into the
code pane that you see on right.

On the worksheet, with a data in A1, you write a formula like

=EOQuarter(A1)

and format the cell with a date format.


On Tue, 13 Sep 2005 13:49:19 -0400, "Gary Keramidas"
wrote:

just a question:
how do you get this to work? i wanted to test it but nothing happens when i
make an entry