Converting a 4 digit number to time

Question

I have just changed to 365 from an older version of Excel. I have a large amount of data that I import from CSV, which has times recorded as 4 digit numbers. I need to display it as a time (ie 1330 to 13:30). I don't need it for any further calculations. In the past, I have used a custom formula of 00\:00 which worked well for the last 15 years or so, but 365 tells me I can't use a custom formula, and everything else I've tried ends in a sea of #######. Is there a simple workaround?&nbsp;

mangosto · Answer

MabeyC&nbsp;&nbsp;Hi!Using Excel 365 allows me to insert the following function:&nbsp;=TIMEVALUE(TEXT(A1,"00\:00"))&nbsp;You should just then adjust the cell format to hours. Change cell A1 to your actual 4 digit reference cell!

mabeyc · Answer

MAngosto&nbsp;Thank you, that worked on the data, but it all goes sideways, if I try to copy and paste it somewhere else and my screen fills up with #ref

sergeibaklan · Answer

MabeyC&nbsp;
Any formula could give such effect. To avoid it paste as values.
Alternatively you may Ctrl+1 on the cell(s) and apply custom format

With that simple paste, not paste as value.
Assuming you perform no calculations with such values.

Forum Discussion

Converting a 4 digit number to time

3 Replies

Resources