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

  Zak 19:39 18 May 10
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 10

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 10

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

  isca2 20:24 18 May 10

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

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

The Legend of Zelda Breath of the Wild review: Five hours with Zelda on the Nintendo Switch

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…