Solved Why is cell format not working

July 13, 2019 at 14:17:17
Specs: Windows 10
Hi

I downloaded a file in excel and I have the time come up as follows in rows:-

741
952
1010
843
1244
1244
809

I have right clicked and selected format cells to select 13:30 in the time / location US in format cells command and get this instead.

0:00
0:00
0:00
0:00
0:00
0:00
0:00

no matter what format I try I get this. Any Ideas?


See More: Why is cell format not working

Reply ↓  Report •

#1
July 13, 2019 at 14:41:47
The data need to be in a Time format, ie

7:41
9:52
10:10

other wise Excel just sees numbers.
The colon tells Excel the data is a Time.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#2
July 13, 2019 at 14:53:06
✔ Best Answer
Here is quick formula to convert your numbers to time:

Copy your data to column A

In cell B1 enter the formula:

=TIMEVALUE(IF(LEN(A1)=3,LEFT(A1,1)&":"&RIGHT(A1,2),LEFT(A1,2)&":"&RIGHT(A1,2)))

Drag down as many rows as needed.

Then on column B do a Copy / Paste / Values.

See how that works for you.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#3
July 14, 2019 at 13:17:43
Big thanks all sorted.

Reply ↓  Report •
Related Solutions


Ask Question