Problems with time formats

[Mr_bikahs]

Hello all,

Was wondering if anyone could help me with an issue I am having. I use a call centre management software to import data into a spreadsheet. However I am having a problem with inconsistent data time formats. Here is an example of the imported data.

[B]Type[/B]                 [B] Time[/B]
 <table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2">  <tbody><tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt; width: 48pt;" width="64" height="17">Break</td>   <td class="xl65" style="width: 48pt;" width="64" align="right">01:20 
</td>  </tr>  <tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt;" height="17">Holiday</td>   <td class="xl65" align="right">07:30</td>  </tr>  <tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt;" height="17">Lunch</td>   <td class="xl65" align="right">00:45</td>  </tr>  <tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt;" height="17">Absence</td>   <td class="xl65" align="right">00:45</td>  </tr>  <tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt;" height="17">Open-Time</td>   <td class="xl66" align="right"> 18:10:00</td>  </tr>  <tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt;" height="17">Total</td>   <td class="xl66" align="right">28:30:00</td>  </tr> </tbody></table>

The first 4 lines are fine because in the formula bar, these values show as "01:20:00" (for break for example). With the Open Time Code, this shows the seconds. Again this is fine as I can just do a custom format change to "hh:mm".

With the Total though, it appears in the formula bar as "01/01/1900 04:30:00"

Does anyone who has experience of weirdness when importing data know what it going wrong here?

I wouldnt have such a big problem here if I had to just merely display these values as it shows correctly in the cell. But I need to use these values in calculations so I need it to be in the correct type (hh:mm). Anyone know a work around I could use?

Obviously, I have tried to change the format to hh:mm. When I do this, it takes away the date "01/01/1900" and just leaves 04:30:00 which is obviously not the correct value and not what I want.

I have also done a find/replace to take out the date but this of course just leaves me with 04:30:00

Anyone help would be greatly appreciated.

 

[Jonmo1]

Well it is technically correct, in what way is it wrong for you?

There is no such time as 28:30:00 (there are only 24 hours in a day, not 28).
So excel reads 28 hours as 1 day and 4 hours, making the time 04:30:00.


to display it as elapsed time, instead of actual day time, format the cell as custom [hh]:mm:ss

Hope that helps.

 

[Mr_bikahs]

Good point you are correct! foolish me,

Thanks jonomo1