Help with Date Formatting

[kamalmba]

Hello All,

I've a column which has dates and the format is not uniform. Some cells have this format (mm/dd/yy hh:mm:ss AM/PM) and few of the cells in this format (dd/mm/yy hh:mm:ss AM/PM).

Is there a way to get a uniform format in all my cells i.e. (mm/dd/yy hh:mm:ss AM/PM) this format.

Thanks in advance.

Regards,
Kamal.

[Richard Buttrey]

Hi,

Do you mean that the cells APPEAR to be different to what you want to see or that the formats that are applied to the cells are indeed different.

Upload the workbook and manually add the results you want to see in an adjacent column.

[kamalmba]

Hi Richard,

Here's the workbook. Column A has the dates which is as is extract from the tool that we use (Though the extract is for the month of Jul'16) and Column B (updated only few cells) is the result that i wish to see. I selected the entire column and changed it to a common format yet no change.

Regards,
Kamal.

[Richard Buttrey]

Hi,
Your problem is nothing to do with formatting, it's because your column A values are a mixture of Text and numbers. The right aligned values are numbers and the left aligned values are text.

You need to look at the tool you are using to extract the values. Check what parameters'/setting it allows you to use if any, and if it does output the values using your Regional Control settings for dates, i.e. mm dd yyyy. At the moment it's outputting using d/m/y -e.g. A3, and of course in A2 Excel can't work with a month 26..

Unless you're able to correct the output from your tool then the only other way is to use a helper column to create proper data numbers by using an IF test to determine if column A is text and if it is use string slicing to create a date number.