24 hour clock time

zebra7860

Active Member
Joined
Jan 9, 2004
Messages
395
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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,931
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
395
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,203
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,794
Messages
5,626,924
Members
416,209
Latest member
tan21

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