title: Converting 4 digit numbers to time format in Excel
body: <p>This was marked as off topic - I hope that this much more long winded question will bring it back on topic.  I have left the original question below for reference.</p>
  
  <p>Original question:</p>
  
  <blockquote>
    <p>I am importing a large text file into excel, this file consists of
    hundreds of times in the format hhmm.</p>
    
    <p>How they are displayed is of no relevence, I am happy with hhmm or
    hh:mm, however I need excel to recognise them as 24 hour times.</p>
    
    <p>Is there a simple way to convert them all, or would it be easier to
    use a formula when adding/subtracting the times from each other to
    convert the time before calculating?</p>
    
    <p>I hope that makes sense.</p>
  </blockquote>
  
  <p>I am a bus driver, as such I work shifts and these shifts are individually unique.  Unlike a factory where multiple people will have the same shift, no bus driver has the same shift as that would mean we would both be driving the same bus at the same time.</p>
  
  <p>Monday to Friday, there are just under 500 individual duties, with about 300 different duties on a Saturday and a further 100 on a Sunday.  So we are looking at a total of about 900 duties.</p>
  
  <p>Each duty has its on detailed duty card, but there is also a duty summary that condenses the information down to start, break and finish times and locations (of which there can be between 1 and 5 of each).  This is part of what I am working with.</p>
  
  <p>Second to the duty summaries are the rotas themselves.  There are numerous rotas depending on what routes we are allocated, but the all follow the same format.  They vary in length from rotating every 4 weeks to every 70 weeks.  Each line on the rota has 7 entries (1 for each day of the week), and the entries either show a duty number or a rest day.</p>
  
  <p>It is then up to us to cross-reference the rota to the duty cards to fill out our diaries to make sure we turn up on time.</p>
  
  <p>What I am trying to do is 3 things:</p>
  
  <ol>
  <li><p>Combine the summary with the rota so there is no more need to
  manually cross reference.  And to be fair, this I have managed.</p></li>
  <li><p>Create an export file to update Google Calendar with multiple rota
  lines at a time, to save manually inputting everything. - Again,
  this I have managed.</p></li>
  <li><p>To calculate the driving hours so we can work out how much time we
  have for overtime - This I have yet to accomplish due to converting
  the time from 4 digits base 10 to hh:mm (with or without the ":").</p></li>
  </ol>
  
  <p>I am currently copying the data from a PDF, pasting into a text file, concatenating each duty into 1 line so that the duty number matches up with the line number in the spreadsheet to make the formulas easier, and then importing that text file into Excel.</p>
  
  <p>I have tried importing from text as space deliminated or copying the text in and using text to columns with space delimination.</p>
  
  <p>The problem is I cannot get Excel to see the times as times. I really don't care whether or not the colon is visible as a separator between the hours and minutes, I just need Excel to recognise the hours and minutes.</p>
  
  <p>But when I select "Format Cells" > "Time" it alters the result.</p>
  
  <p>For example, 1045 should be read as 10:45.  However, when I convert the cell from "general" to "time", 1045 gets converted to 10/11/1902  00:00:00.</p>
  
  <p>If I try using custom formatting the following results:</p>
  
  <ul>
  <li><p>hh:mm = 00:00 </p></li>
  <li><p>[hh]:mm = 25080:00</p></li>
  </ul>
  
  <p>And this is why I asked the question.  Simply formatting the cells doesn't render them calculable and with well over 3000 times, there are too many adjust manually.</p>
  
  <p>I will settle for using a formula to convert a 4 digit base 10 number into hh:mm when calculating the driving hours, but I would rather Excel just recognise the data for times.</p>
  
  <p>I hope this makes more sense and brings this back on topic.</p>