Last day of the month
 

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.

Last day of the month
 

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

Last day of the month
 

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.

Last day of the month
 

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

Last day of the month
 

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

Last day of the month
 

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

Last day of the month
 

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

Last day of the month
 

PS: If you still can't get it to work, I can send you a workbook with the code
in it.

On Tue, 13 Sep 2005 19:32:00 -0500, Myrna Larson
wrote:

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

Last day of the month
 

Another option here is to use the COUPNCD function from the ATP, specifying
the date of interest as the "settlement" date, the end of a quarter as the
"maturity" date, a frequency of 4 and basis of 1.

The constraint here is that the 2nd date (end of a quarter) must be later than
the 1st date.

I just tried this, and the results are identical, but the VBA function is
faster by a factor of about 2.


On Tue, 13 Sep 2005 19:42:53 -0500, Myrna Larson
wrote:

PS: If you still can't get it to work, I can send you a workbook with the
code
in it.

On Tue, 13 Sep 2005 19:32:00 -0500, Myrna Larson
wrote:

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

Last day of the month
 

thanks, i got it to work, needed =EndOfQuarter(a1), though.

i knew about where to put the code, just wasn't sure about the "function"
part of it. i have seen some work upon entry, but i guess this one didn't.
plus, i didn't know to enter =EndOfQuarter.

thanks, i learned something

--


Gary


"Myrna Larson" wrote in message
...
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