S
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?
month and if not change it to the end of the month?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
ryguy7272
=EOMONTH(A1,0)
Format as any type of date you want.
HTH,
Ryan---
Format as any type of date you want.
HTH,
Ryan---
R
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)
=IF(DAY(A1+1)>1,DATE(YEAR(A1),MONTH(A1)+1,0),A1)
R
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)
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)
R
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
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
S
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
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
S
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.
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.
R
Rick Rothstein
It should be as simple as this:
Try it this way...
For Each Cel In Selection
Cel.Value = DateSerial(Year(Cel.Value), Month(Cel.Value) + 1, 0)
Next
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
S
Stratuser
That worked. Thanks very much.
Rick Rothstein said:Try it this way...
For Each Cel In Selection
Cel.Value = DateSerial(Year(Cel.Value), Month(Cel.Value) + 1, 0)
Next