how to change MM/DD/YYYY HH:MM:SS to DD/MM/YYYY HH:MM

**pikelnpnut** · 03-01-2012, 02:35 PM

I'm hoping someone with some expertise can assist me,

I have Excell 2007 and am having challenges with the date and time showing in the correct format.

I've tried highlighting the column with the dates, then go to Date -> More Number formats and on the number tab selecting Date, Type 14/3/2001 13:30 with the locale (location) Hebrew (Israel) and then click OK. This has not worked for any days numbered 1-12. These revert to the MM/DD/YYYY H:MM:SS AM format

I've also tried hightlighting the column with the dates, then go to the Data Tab -> Text to Columns -> Delimited -> Tab -> on the Column data format clicking Date and choosing DMY and then Finish. This also has not worked for any days numbered 1-12. These revert to the MM/DD/YYYY H:MM:SS AM format

Thank you

**ChemistB** · 03-01-2012, 02:44 PM

It should work the way you did it. You can also try Number>Custom>dd/mm/yyyy (keeping it English US)
Does that work?

**dilipandey** · 03-01-2012, 03:01 PM

Hi Pike,

Welcome to the forum.

As long as the date is in DATE format, the format change should work. First you need to check if the date is really date... try extracting the month using =Month(reference to date), if this is successful then the date is in DATE format, so go ahead and just change the format.

If the date is in TEXT format, you need to convert that into DATE format first.. and then you can change the format easily. Try uploading a sample file, if you have any issues here. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**pikelnpnut** · 03-09-2012, 02:02 PM

Thanks DILIPandey and ChemistB. I've contacted Excell tech support. They've remoted into my computer and aren't able to get things to be the correct format either. We've come up with the temporary solution of putting an apostrophe in front of the date and time sequence which seems to hold the format. Thanks again for your input!
Smiles,
Cynthia