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?

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

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Stratuser" wrote:

> 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?
>
>

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 (MVP - Excel)


"Stratuser" <(E-Mail Removed)> wrote in message
news3A36E2A-21CB-4FC1-91A9-(E-Mail Removed)...
> 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?
>
>

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 (MVP - Excel)


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 (MVP - Excel)
>
>
> "Stratuser" <(E-Mail Removed)> wrote in message
> news3A36E2A-21CB-4FC1-91A9-(E-Mail Removed)...
>> 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?
>>
>>
>

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

--
Rick (MVP - Excel)


"Stratuser" <(E-Mail Removed)> wrote in message
news3A36E2A-21CB-4FC1-91A9-(E-Mail Removed)...
> 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?
>
>

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

"Rick Rothstein" wrote:

> 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
>
> --
> Rick (MVP - Excel)
>
>
> "Stratuser" <(E-Mail Removed)> wrote in message
> news3A36E2A-21CB-4FC1-91A9-(E-Mail Removed)...
> > 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?
> >
> >
>
>

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" wrote:

> 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
>
> --
> Rick (MVP - Excel)
>
>
> "Stratuser" <(E-Mail Removed)> wrote in message
> news3A36E2A-21CB-4FC1-91A9-(E-Mail Removed)...
> > 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?
> >
> >
>
>

> 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

--
Rick (MVP - Excel)

That worked. Thanks very much.

"Rick Rothstein" wrote:

> > 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
>
> --
> Rick (MVP - Excel)
>