fix to change data from 12 hour clock to 24 hour

1overpar

New Member
Joined
Jan 26, 2005
Messages
6
I'm having a problem with data that's been collected because it's been entered as a 12 hour clock and not a 24 hour clock. So I have reams of data of times that I need to convert in order to analyse.

So my dilimma is that I need to change 1:15 to 13:15 and so on and so on. I was hoping to do a replace 1:** with 13:** but that didn't work.

I then tried =A1+12 but that didn't work either.

If anyone has any ideas, I would love to hear them.

Thanks :biggrin:
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
Have you tried formatting the cell?


If it is already formatted as 13:30

and it still says 1:30, Try adding .5
 

1overpar

New Member
Joined
Jan 26, 2005
Messages
6
I tried formatting to no avail. Can you clarify what you mean by adding .5?

THanks,
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
Excel treats times as a number.

1 day is 1.

.5 is half a day or 12 hours.

So lets say you were to take 6:00 am.

Excel Sees this as .25

adding .5 to this makes it .75 or 6:00 PM.
 

1overpar

New Member
Joined
Jan 26, 2005
Messages
6
Absolutely brilliant! Many thanks for literally saving me weeks. I was hoping that there would be a simple solution.

Cheers!
 

Forum statistics

Threads
1,148,530
Messages
5,747,238
Members
424,070
Latest member
smanni3

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