24 hour clock time

[zebra7860]

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

 

[Aladin Akyurek]

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]

Hi Al,

format column C to 24hrs time

Eli

Book1
	A	B	C	D	E	F
1	11:30	am	11:30
2	2:40	pm	14:40
3
4
5
6
7
8
9
10
11
12
Sheet1

 

[zebra7860]

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]

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]

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?