How does one convert 3-4 digit numbers into a time?

Wulf

Active Member
Joined
Dec 1, 2004
Messages
395
Office Version
  1. 365
Platform
  1. Windows
I have 7522 cells that have numbers in them. They're SUPPOSED to be times. I was asked if these cells (part of a larger worksheet) could be converted into times. I've been screwing with it for several hours, trying to come up with a formula, but so far nothing works the way I need it to.

I need "2200" to read as "22:00", "300" to read as "3:00", "1500" to read as "15:00" etc.

How can this be done?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Wulf

Active Member
Joined
Dec 1, 2004
Messages
395
Office Version
  1. 365
Platform
  1. Windows
Hi

Try:

=TEXT(A1,"00\:00")+0

Amazing how simple things can be......

Thank you very much, that did it!
 

joefrench

Active Member
Joined
Oct 4, 2006
Messages
357
See sheet below. Ignore the last two cells! :p
=CONCATENATE(REPLACE(J9,3,2,""),":",REPLACE(J9,1,2,""))
Of course, this will only work with the 4 digit values. If you want to take care of 3 digits as well, you will have to alter accordingly or add an if statement to determine the number of characters in the cell.
personal.htm
JKLM
9220022:000022:00:00
personal
 

Forum statistics

Threads
1,181,730
Messages
5,931,718
Members
436,800
Latest member
abowalid98

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