Convert Date to End of Month

[Stratuser]

If cell "a1" has a date, how could I check whether the date is the end of the
month and if not change it to the end of the month?

 

[ryguy7272]

=EOMONTH(A1,0)
Format as any type of date you want.

HTH,
Ryan---

 

[Rick Rothstein]

Here is another way which doesn't require the Analysis ToolPak...

=IF(DAY(A1+1)>1,DATE(YEAR(A1),MONTH(A1)+1,0),A1)

 

[Rick Rothstein]

I got hung up on your "how do you test" question that I didn't pay attention
to what you ultimately wanted. This simpler formula will yield the end of
the month date for the date specified in A1...

=DATE(YEAR(A8),MONTH(A8)+1,0)

 

[Rick Rothstein]

Is this a VB question by any chance? And if so, did you want to physically
change the date in A1 from whatever it is to the end of its month (thus
losing whatever was in the cell to begin with)? If the answer to both of
these questions are "Yes", then try this macro out...

Sub ChangeDateToEndOfMonth()
With Range("A1")
.Value = DateSerial(Year(.Value), Month(.Value) + 1, 0)
End With
End Sub

 

[Stratuser]

I have a very old Excel program that imports data with dates, but I have
recently started working in Excel 2007. I'm trying to check each of the
dates in VBA to make sure that each one is the end of the month. For some
reason, the Eomonth command is not working in the old worksheet, although the
same code works fine when I've tested it by entering the data by hand in a
test worksheet. This has me thinking that the problem may be the data
itself, not the code.

Basically, the code is like this:

For each cell in Selection

Next cel

 

[Stratuser]

It's a VBA question. I'm trying to make sure all the dates in a selected
range of imported data are the end of their respective months. The code I
have works fine in a test worksheet when I enter the data manually, but not
in an old worksheet. This makes me wonder whether I have a data problem.
Maybe Eomonth is not evaluating the dates in the old worksheet correctly.

It should be as simple as this:

For each cel in Selection
cel.value = Eomonth(cel.value, 0)
Next cel

But when my code sees 4/30/2006 in the old worksheet, Eomonth returns 31.

 

[Rick Rothstein]

It should be as simple as this:

For each cel in Selection
cel.value = Eomonth(cel.value, 0)
Next cel

Try it this way...

For Each Cel In Selection
Cel.Value = DateSerial(Year(Cel.Value), Month(Cel.Value) + 1, 0)
Next

 

[Stratuser]

That worked. Thanks very much.

Try it this way...

For Each Cel In Selection
Cel.Value = DateSerial(Year(Cel.Value), Month(Cel.Value) + 1, 0)
Next