BT Bill call duration time in odd format - VoG™?

  Zak 19:39 18 May 2010
Locked

Hi VoG™

I have a problem with BTs new on-line calls list. CSV file when opened with Excel now shows in an odd format. It used to be in 00:59:25 (hh:mm:ss)and appear in formula bar as 00:59:25, 00:50:22 etc.

Now there are two variations and Excel treats the minutes and seconds as hours, minutes and seconds.

Formula bar shows: 01/01/1900 11:45:00 and cell shows 11:45:00 This should be 11 minutes 45 seconds

Other instances formula bar shows: 20:03:00 (should be 20 minutes and three seconds) and cell shows same, no logic as to size of numbers.

Is there any way to automate the conversion to the normal hh:mm:ss?

Previous bills always had the leading zeros for the hour when under an hour and did not have additional zeros at he end.

I use the calls to check per minute rate for calls abroad via access numbers.

  VoG II 19:52 18 May 2010

Say your times start in A1, in a spare column

=TIME(0,HOUR(A1),MINUTE(A1))

and copy down as far as needed.

  Zak 19:59 18 May 2010

Thank you very much for such a quick response.

Just followed your instructions and all is in correct format and my spreadsheet now works correctly.
Thank you again.
Kind regards

Zak

  iscanut 20:24 18 May 2010

Well done Voice of God or Very Old Git ??
What service !

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Intel Coffee Lake release date and specifications

These beautiful, mesmerising & subtle GIFs celebrate 30 years of the artform

watchOS 4 review

Les meilleurs navigateurs internet 2017