change date to last day of month

rsvinden

New Member
Joined
Apr 27, 2009
Messages
25
I have a large worksheet that contains dates in one column. I need all dates to be the last day of the month. Example: if the date is 5/26/2010 I need for it to be 5/31/2010, if 2/05/2008 S/B 2/28/2008. I am doing it by sorting and changing now. The list is growing to over 50,000 lines. Any help would be appreciated.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
I have a large worksheet that contains dates in one column. I need all dates to be the last day of the month. Example: if the date is 5/26/2010 I need for it to be 5/31/2010, if 2/05/2008 S/B 2/28/2008. I am doing it by sorting and changing now. The list is growing to over 50,000 lines. Any help would be appreciated.
If you want to use a formula in another column...

A2 = some date

Enter this formula in B2:

=EOMONTH(A2)

Format as Date

Copy down as needed.

Note that the EOMONTH function requires the Analysis ToolPak
add-in be installed if you're using a version of Excel prior to
Excel 2007. If you enter the formula and get a #NAME?
error look in Excel help for the EOMONTH function. It'll tell you
how to fix the problem
 
Upvote 0
You can use this formula to return the last day of the month of the date in A2.

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

Copy it down for all the dates you have.
 
Upvote 0
Thanks to all that gave a solution. I tried the =EOMONTH(A2,0) but the year did not turn out correct. The =date formula worked. I was able to convert my data in seconds compared to hours before.
Thanks again:)
 
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,849
Members
452,948
Latest member
UsmanAli786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top