Switching dates to end of month

P

Peter Bernadyne

Hello,

I have a bunch of historical observations with a date record for eac
as follows:

Jan-1950
Feb-1950

and so on. These dates all default to the first day of the month s
that technically Excel is reading these as Jan-1-1950, etc.

Is there an easy way to automatically convert these dates to th
end-of-the month period, instead? I've tried adding 30 days, etc. bu
this is inaccurate because some of the months have 30 days, some hav
31, not to mention 28 with February.

Any ideas would be very much appreciated!

Thanks,

-Pete
 
B

Bob Phillips

Assuming the dates are in A1:An

in A1
=A1-DAY(A1)+DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

copy the formula down. Then copy and past values in column B and delete
column A.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

Ron Rosenfeld

Hello,

I have a bunch of historical observations with a date record for each
as follows:

Jan-1950
Feb-1950

and so on. These dates all default to the first day of the month so
that technically Excel is reading these as Jan-1-1950, etc.

Is there an easy way to automatically convert these dates to the
end-of-the month period, instead? I've tried adding 30 days, etc. but
this is inaccurate because some of the months have 30 days, some have
31, not to mention 28 with February.

Any ideas would be very much appreciated!

Thanks,

-Peter
Click to expand...

If you have the Analysis Tool Pak installed, you can use:

=EOMONTH(A1,0)

If you do not have/want the ATP installed, since your values are all the first
of the month, you could use the formula:

=A1+32-DAY(A1+32)

A more general formula that works regardless of the day of the month would be:

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




--ron
 
Top