Date/Month/Year formatting for column "c" not working for me...

[Rabbitstew]

Hi Everyone,

Can someone write a macro for me that will change column "c" to be exactly Month/Day/Year?

I tried recording a macro to do this but the results end up like the following:

I would like the results to be consistent like this:

Thanks in advance!

[Special-K]

Just format as date and select the option 14-03-2001

[nemo74]

Hi Rabbit,

Looks like you have some dates that are not formatted correctly. I'm sure you could fix this with a macro, but you can also do it with out. Create another column next to your dates.

Select the date column and use text to columns. Fixed Width, Let it split the hours off, and then choose the Date: button and change the drop down to anything else.

Finish

You can now delete the extra column you created for the time and format the date column like Special-K said.

Questions ?

[Rabbitstew]

Hi Guys,

Thanks for your answers, I tried to do the text to columns but for some reason dates that start with a single digit from 1-9 end up with a "(space)0:00". When I tried doing a find and replace for "(space)0:00" it says Excel cannot find the data you're looking for when clearly I see it right before my very eyes. Is there a macro that can add "0" to any dates that start with a single digit 1-9 that I can run before doing the text to data thing?

Before:

4/5/2013 8:00:00 PM

After:

4/5/2013 0:00

I can't get rid of the 0:00 for some reason - any help would be greatly appreciated - Thanks in advance!

ps: is there an option on this site to notify you via email when someone responds to your questions?

[Rabbitstew]

Hello? Can anyone help?

[nemo74]

Either you where not clear on what you wanted or you are not following my text to coloumn directions.

[Rabbitstew]

Hi Nemo,

I've followed your instructions to the T.

I selected the date column, did text to columns and moved the break line to desired position (right after the year), I selected "next", selected date: MDY. Clicked on finish and selected okay to the "do you want to replace contents of the destination cells". After all that's said and done, I end up with dates looking like this:

28/04/2013

and

4/5/2013 0:00

Call me crazy but I'm pretty sure I followed the instructions correctly. If I'm missing something please let me know.

Thanks again for your help, I greatly appreciate it!

[jakestyeen7059]

If you want in macro,

Please Login or Register  to view this content.

But you could always just select column C, right click and format cells, go to Date, and find the type you want.

Hope this helps

[protonLeah]

Post a workbook with some of the dates that you are having trouble with.
To Attach a Workbook:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit

[Rabbitstew]

Thanks for your help guys, please see attached!

The culprit is date column C.

[Martijnvc]

Two steps, no macro necessary.

1. As mentioned before, insert column right to C and enter the following formula:

Please Login or Register  to view this content.

2. Format column D using custom number format (depending on system settings, check standard formatting under date as well)

Please Login or Register  to view this content.

[protonLeah]

Because many of your entries are text values and not date values, insert a new column D and use:

Please Login or Register  to view this content.

[Rabbitstew]

YES - It Works protonLeah! THANK YOU!!!