asked on

Format 12hr time and AM/PM in different columns to 24hr time in a single column? EXCEL

Hi Experts,

I have 12 hour time and AM/PM in different columns.

I need 24 hour time if at all possible.

From

11:32:46      PM
11:44:28      PM
12:29:20      AM
12:35:44      AM

23:32:46
23:44:28
00:29:20
00:35:44

Can you provide sample worksheet with current data?

Is the time you have currently a time field, or a text field?

~bp

Negativ3

ASKER

No problem at all, please find attached, thanks Bill for the rapid response!
DATA---Copy.xlsx

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

Or try this...

On Sheet2
In C2

=IF(B2="AM",MOD(A2,0.5),A2)

Open in new window

and custom format it with "hh:mm:ss"

Shums Faruk

or try this:

=IF(B2="AM",A2,A2+TIME(12,0,0))

Open in new window

DATA---Copy.xlsx

Bill Prew

At this point we have 3 different solutions, that interestingly produce slightly different results. I built worksheet of the input values, and then the three results that our approaches produced. It might help the poster review the results and see which best met their needs. I added a column to the right of each solution results column and flagged rows that didn't match the desired result (assuming I understood that correctly).

~bp

Validate.xlsx

Negativ3

ASKER

Perfect solution Bill, thank you.