Help please converting time formatting

[dan_fash]

I am working from an exported data set of Labratory tests, and I need to time from patients arrival to tiem they had a test performed.

The exported file has arrival date and time in one cell, and I use "text to columns" to separate date an time.
The date changes to a standard short date format - great, that what I need
The time is just a "general" format, ie 11:14PM
however I need time to be in military format so I can calculate change in time. so I need 11:14PM to read 23:14, but when I change the formatting of the cell to military time, the cell still reads 11:14PM

Any help greatly appreciated

(I attempted to attach a file, but the website says I do not have permission to perform that task)

[dan_fash]

I think this let me attach the file

[dan_fash]

by the way, I did attempt to separate the date and time with =int(A2), but only received #value!

[Pepe Le Mokko]

To extract date =LEFT(A2,10)*1 and format as needed
To extract time =SUBSTITUTE(RIGHT(A2,LEN(A2)-12),"PM", " "&"PM")+0 and also format as needed

[dan_fash]

nope, tried that one already also. doesn't work... I can get the date out just fine with Text to columns" just can't get the time out

[shg]

If you select column A and find & replace "AM" with " AM" and "PM" with " PM" (all sans quotes) the values willl be converted from text to dates. Then you can format however you wish.

[Pete_UK]

Without changing anything else in your file, you can get the date using this formula (e.g. in H2):

=DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2))

and the time using this formula (e.g. in I2):

=TIMEVALUE(MID(A2,12,5)&":00 "&RIGHT(TRIM(A2),2))

Format I2 as a time, then copy the formulae down.

Hope this helps.

Pete

[samba_ravi]

C2=--TEXT((MID(TRIM(A2),12,LEN(TRIM(A2))-13)&" "&RIGHT(TRIM(A2),2)),"hh:mm")
TRY THIS AND COPY TOWARDS DOWN

[Pepe Le Mokko]

nope, tried that one already also. doesn't work... I can get the date out just fine with Text to columns" just can't get the time out

Strange, works perfectly for me

[dan_fash]

thank you all, got it!