converting numbers to time
I have a list of numbers:
900 1030 830 etc. I need these in the format: 09:00:00 10:30:00 08:30:00 etc I have tried text to columns but without the 0 before 830 the fixed width line doesnt fall right. Very stuck on this so any help would be gratefully recieved (otherwise I have alot of work on my hands!!) |
converting numbers to time
Assumes C5 has 900 and Time format set to 00:00:00
HTH Rob =TIME(INT(C5/100),MOD(C5,100),0) "FishandChips" wrote in message ... I have a list of numbers: 900 1030 830 etc. I need these in the format: 09:00:00 10:30:00 08:30:00 etc I have tried text to columns but without the 0 before 830 the fixed width line doesnt fall right. Very stuck on this so any help would be gratefully recieved (otherwise I have alot of work on my hands!!) |
converting numbers to time
Hi,
Assuming your numbers are in a1 down try this =VALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2)) Format as time -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "FishandChips" wrote: I have a list of numbers: 900 1030 830 etc. I need these in the format: 09:00:00 10:30:00 08:30:00 etc I have tried text to columns but without the 0 before 830 the fixed width line doesnt fall right. Very stuck on this so any help would be gratefully recieved (otherwise I have alot of work on my hands!!) |
converting numbers to time
900
1030 830 etc. What times do those represent? Is 900 9:00 AM or 9:00 PM Is 1030 10:30 AM or 10:30 PM? etc -- Biff Microsoft Excel MVP "FishandChips" wrote in message ... I have a list of numbers: 900 1030 830 etc. I need these in the format: 09:00:00 10:30:00 08:30:00 etc I have tried text to columns but without the 0 before 830 the fixed width line doesnt fall right. Very stuck on this so any help would be gratefully recieved (otherwise I have alot of work on my hands!!) |
converting numbers to time
=--TEXT(A2,"00\:00") and format as time.
-- David Biddulph FishandChips wrote: I have a list of numbers: 900 1030 830 etc. I need these in the format: 09:00:00 10:30:00 08:30:00 etc I have tried text to columns but without the 0 before 830 the fixed width line doesnt fall right. Very stuck on this so any help would be gratefully recieved (otherwise I have alot of work on my hands!!) |
All times are GMT +1. The time now is 03:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com