Time Format Help Needed (Excel)

E

Erin

I am working with data in Excel and have time stamps in the format of 24hr
time but it does not have a colon. For example 1321 or 843 instead of what I
want 13:21 or 8:43.

Is there a way to either add a colon or change the format into a time
format. (I already tried just highlighting to change the format to time; it
doesn't work.)
 
A

Ashish Mathur

Hi,

Assuming you have typed in 1321 in cell A1, enter the following formula in
cell B1

LEFT(A1,IF(LEN(A1)=4,2,1))&":"&RIGHT(A1,2). Now format the cell to Time.

Regards,

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
P

Peo Sjoblom

First of all that formula will return text so it doesn't help if you format
as time

=--(LEFT(A1,IF(LEN(A1)=4,2,1))&":"&RIGHT(A1,2))

then format as time, however if 10 means 00:10 then your formula fails




but if are going to use a formula then it is easier to use

=TIME(INT(A1/100),MOD(A1,100),0)

and even easier to use

=--TEXT(A1,"00\:00")


--


Regards,


Peo Sjoblom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Countdown macro - display settings 2
Excel does not accept text format! 0
Publisher 2019 Format Picture window size too large to display OK button 0
Word doc keeps changing to ALL CAPS 0
Average time 15
Problem formatting dates from CSV file 2
Peculiar default cell format 1
VBA - exporting serial letters to single documents 0

Top