How can I display a text value in time format.

Jogi

New Member
Joined
Aug 2, 2002
Messages
19
I have a worksheet where I update a column with time. It's set up as a text - so I can enter 1800 iso 18:00 - to save time. Is there a way I can enter time without having to enter the colon or is there a formula which will convert this for me.

Thanks for any replies!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
Assuming your time is always entered as four characters of text, eg 0600 for 6:00 AM, you could enter this in an adjacent cell

=(LEFT(A1,2)&":"&RIGHT(A1,2))+0

and format as time.

HTH

Richard
 

Jogi

New Member
Joined
Aug 2, 2002
Messages
19

ADVERTISEMENT

Thanks Paddy!

I am learning a lot from this Board!!
 

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
I've used something similar. I formatted the cells to 00:00, then the user may enter times as 1000 and get 10:00. In another cell, I convert that text to time

=(LEFT(REPT("0",4-LEN(a1))&a1,2)&":"&RIGHT(a1,2))+0
 

Beh162

Board Regular
Joined
Jan 15, 2015
Messages
125
I'm actually curious on how to use this with my sheet because it's easier to type 1340 rather than 13:40 when you have to do it 40 times lol. I have a block of about 40 cells that I key times in. Where could I enter the formula in?
 

Forum statistics

Threads
1,148,277
Messages
5,745,818
Members
423,980
Latest member
zimza

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