Converting a time value to 24hr Clock

**Sugar Ape** · 10-08-2009, 05:07 AM

I have a worksheet that contains a list of time values in 12hr clock format, (e.g., 02:30 PM). that i need to convert in to 24hr clock format, (e.g., 14:30). No matter what i do, Excel always seems to regard the 12hr clock format as text.
Does anyone know a simple way to convert?

Thanks
Stu

**DonkeyOte** · 10-08-2009, 05:16 AM

Are you sure they are true time values ?

What does =ISNUMBER(A1) return where A1 is meant to represent a cell containing the time ?

If it's FALSE you need to coerce your times store as text to time (decimal numbers) - to do en masse - highlight column and run Data -> Text to Columns -> Finish and then alter format of cells accordingly

**Sugar Ape** · 10-08-2009, 05:35 AM

Thanks. No matter what I did I couldn’t force the cell to be a number value. Even when I changed the format and did text to columns it wouldn’t play ball. I ended up going round the houses by dividing the cell up in to 3 separate cells and using an if statement to add 12 to the hour value if the value of an adjacent field was “PM”. Then had to put it all back together adding zeros to any values less than 10…

There must be a better way

**DonkeyOte** · 10-08-2009, 05:37 AM

Yes, you should be able to override the original values but we'd need to see some sample data...

What does

=FIND(CHAR(160),A1)

return ?
again where A1 is original time value

**Sugar Ape** · 10-08-2009, 09:29 AM

Attached is a sample of the original data. I was splitting this in to 2 parts and just working on the time but have left it whole in this example.
What I actually need to do is calculate the time between two date/time values in this format.

I need to do this on a regular basis so if there's a quicker way than mine i'd be very grateful.

**DonkeyOte** · 10-08-2009, 09:36 AM

The issue (as suspected) is CHAR(160) -- the rightmost character in the values in A is CHAR(160) - quite common if sourced from a website etc...

To convert all the values in A to dates...

Highlight column A
Run Edit -> Replace
Find What: hold ALT and type 0160 on numeric keypad
(if using a laptop hold ALT + FN and type 0160 on numeric keypad (ie the numbers assigned to letters))
Replace With: leave blank
Click OK

... all your values will now be coerced to dates and you can work with them as normal.