quick way formatting Mon Day Year HH:MMAM/PM

[Multiscan]

Hi all,
I was wondering if somebody could point me in the right direction. I got lots of old data in excel and trying to convert e.g. Apr 10 1947 11:00AM (which seems to be text ) to maybe 10/04/1947 11:00 date format so I can calculate minute difference between two columns.

from forum search looks like the only way to do this would be format it first by using subtract/replace/substitute before i can subtract two dates?

Thank you in advance.

[Richard Buttrey]

Hi,

The first thing to confirm is whether it IS text, or is it a pukka date number.

Test with =ISTEXT(A1)

Once we know definitively whether they are text or numbers we'll be able to advise.

[Ace_XL]

In a separate cell use

=(LEFT(SUBSTITUTE(A1," ",", ",2),LEN(A1)-1)&" "&RIGHT(A1,2))*1

Format cell as mmm dd yyyy hh:mm AM/PM

[Multiscan]

Hi Guys , thank you for quick reply.

Hi,

The first thing to confirm is whether it IS text, or is it a pukka date number.

Test with =ISTEXT(A1)

Once we know definitively whether they are text or numbers we'll be able to advise.

Richard,
Yes, looks like both columns are definitely text

In a separate cell use

=(LEFT(SUBSTITUTE(A1," ",", ",2),LEN(A1)-1)&" "&RIGHT(A1,2))*1

Format cell as mmm dd yyyy hh:mm AM/PM

Ace_XL,

I get wrong data type error when using this query. is an idea to remove all spaces and then convert it?

[samba_ravi]

please attach sample excel file for quick and better results

[Richard Buttrey]

Hi Guys , thank you for quick reply.

Richard,
Yes, looks like both columns are definitely text

In that case you do need to upload the workbook so that we can see the request in context and properly understand the format of the text.

[Multiscan]

Greatly appreciated!
Sample now attached

[Ace_XL]

You've got some extra spaces in there..

Try
=(LEFT(SUBSTITUTE(TRIM(A2)," ",", ",2),LEN(TRIM(A2))-1)&" "&RIGHT(A2,2))*1

[samba_ravi]

See the attached file

[Multiscan]

Guys this is fantastic! this was the last piece of my excel puzzle. wasn't sure how to start on this format. Typically all other date columns are in different format and converted fine but not these two.

Thank you kindly.

rgds
Rob

[Richard Buttrey]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.