Converting AM PM into 24hr

[Median]

Hi All,

I've having a nightmare trying to convert AM PM times into 24hr, I keep receiving VALUE errors (Column C)

Column D is the result I'm trying to achieve

Thanks

Mdn

[6StringJazzer]

When I open your file I do not get any errors. Column C is just data values, so cannot return #VALUE errors.

The data in column B is text, rather than time values. The reason is that the 5th character looks like a space, but is actually a non-printable character of some kind (maybe a non-breaking space, not sure), so Excel does not realize this is supposed to be a time value. How are you getting the time data into your file?

You can either clean up your data (preferred), or address this at the formula level.

To clean up your data:
Edit a cell containing the time in column B. Copy the space--just the space character.
Go to Find & Replace
In Find, highlight the box and Paste. This will paste the false space character.
In Replace, highlight the box and press SPACE.
Then Replace All.

To use a formula to calculate the date + time:

Please Login or Register  to view this content.

[Median]

Cheers Jeff, that formula works great

For context the time data is from gmail

Attachment 850330

[6StringJazzer]

Just for completeness, the data could also be cleaned up with VBA, but if you're happy with the formula we'll stop there.

[Rick Rothstein]

@6StringJazzer's formula is shorter, but I would have done it this more "old school" way...

Please Login or Register  to view this content.