24 hour clock time

zebra7860

Active Member
Joined
Jan 9, 2004
Messages
403
hello,

I have times in one column of excel specified as 4:55 a.m or 02:35 pm and the other column has the am and pm's.

All I simply want to do is to change the time to a 24 hour clock, ie: 4:55 = 04:55 and 14:35.


This should be a simple one.

Thanks in advance,
Al
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Custom format your time entries as:

[hh]:mm


Edit: Eli made me re-read your question...

=--(TEXT(A1,"hh:mm")&" "&B1)

and apply the custom format [hh]:mm to the formula cell.
 

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,936
Hi Al,

format column C to 24hrs time

Eli
Book1
ABCDEF
111:30am11:30
22:40pm14:40
3
4
5
6
7
8
9
10
11
12
Sheet1
 

zebra7860

Active Member
Joined
Jan 9, 2004
Messages
403
Hi Eli or anybody else,

Here is what a couple of lines look like on my spreadsheet. Trying your formula and adjusting for parameters didn't work as well as it should have.

2/20/2004 4:00:00 pm 1.2522 1.2524 1.2522 1.2522
2/20/2004 3:55:00 PM 1.2516 1.2523 1.2515 1.2522

where the date is column A, time=B, am/pm=c, etc....

Typing in the formula :

=B1+IF(C1="am",0,0.5) results in a the following result: 1/0/1900 16:00


I AM NOT INTERESTED IN A DATE being inserted and furthermore values between 12:00 noon and 12:55 results in times of 0:00 to 00:55...such values should occur after midnight onwards...

Can anybody help?

Al
 

AlohaJulio

Board Regular
Joined
Feb 10, 2004
Messages
188
If you use this formula, it will add the day to the time, and then format it with HH:MM:SS and you should get 16:00:00, and the underlying value will be for 2/20/2004 4:00PM.
Otherwise it is just fixing the format of the result you agre getting, which is excel assuming the time is on the first day of its calendar giving you the value of 1/1/1901 4:00PM.

=A1+(B1+IF(C1="am",0,0.5))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
zebra7860 said:
Hi Eli or anybody else,

Here is what a couple of lines look like on my spreadsheet. Trying your formula and adjusting for parameters didn't work as well as it should have.

2/20/2004 4:00:00 pm 1.2522 1.2524 1.2522 1.2522
2/20/2004 3:55:00 PM 1.2516 1.2523 1.2515 1.2522

where the date is column A, time=B, am/pm=c, etc....

Typing in the formula :

=B1+IF(C1="am",0,0.5) results in a the following result: 1/0/1900 16:00


I AM NOT INTERESTED IN A DATE being inserted and furthermore values between 12:00 noon and 12:55 results in times of 0:00 to 00:55...such values should occur after midnight onwards...

Can anybody help?

Al

Did you try...

=--(TEXT(B1,"hh:mm")&" "&C1)

and apply the custom format [hh]:mm to the formula cell?
 

Forum statistics

Threads
1,172,162
Messages
5,879,378
Members
433,425
Latest member
calinorth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top