PDA

View Full Version : Changing 'decimal times' to 'real times' throughout a workbook



Brain_Cell_Anon
03-08-2006, 11:03 AM
Hi O respected inteligence ones! :rolleyes:
Being a person of limited fore-thought, I have made up an Excel workbook with all my CDs listed. This is to allow me to check when I'm out on my PDA to see if I've actually bought already the CD I'm about to buy! :thumbs:
In said workbook, the first sheet is an 'index' with hyperlinks to each album listed. On each worksheet is listed a singer/groups albums and/or CD singles with the tracks and their times.
When I started the workbook I entered the track times formated as a number with 2 numbers after the decimal point. Then at the bottom, the sum of the 'times' were added together. But this led to incorrect answers. I, of course, forgot that time is not decimal! :groan:
SO....
Now that I have perhaps twenty worksheets with maybe two or three albums on each sheet, with some 10 to 20 tracks per album, I have suddenly realised that if I could change the cells that are entered in decimal to a time (hh:mm:ss or mm:ss) format, I would get a true result for the album length ( =sum(A5:A20) all the times ).
But how do I go about changing ALL the 'decimal times' to 'real times' in the entire workbook in one go? Is there some Macro I can do? Or, for that matter, is it possible to even do that? :waughh:
If you've read thus far, well done!
Many regards in advanced,
The Cell :D

roddy_boy
03-08-2006, 11:45 AM
Highlight the row/column you want to change, goto: Edit, Replace, Specify find '.' , replace with ':' .
I'm sure someone else will come up with a better solution, but I was just looking quickly for a way to do this, and all this was all I came up with.

Edit: I tried doing it by Format, Cells, Custom, and trying to figure out how to configure it there, but I just ended up confusing the hell outta myself.

odyssey
03-08-2006, 02:28 PM
You can use a formula:

=TIME(0,TRUNC(A1),RIGHT(FIXED(A1,2),2))

where A1 is the cell of the value you want to change.

Using this method I have assumed for example a 4 minute long and 56 second track was entered as 4.56. And also that no songs go longer than an hour!

Note you may want to use custom formatting on your cell to display tracks at mm:ss and album totals as h:mm:ss