I am the new record keeper of my local swimming club, and I have inherited several years of poorly recorded results, which I am polishing up.
I need to find a robust method of entering swim times in the format mm:ss.00 or ss.00 (i.e. 1:23.45 or 12.34).
By ‘robust’ I mean that other people, with little or no knowledge of Excel, also have to be able to enter data into the database without scr*wing it over
I have experimented with the custom format mm:ss.00 but I have the following issues;
1) If I set the format to mm:ss.00 and enter 1:23.45 it stores it correctly.
However if I enter 1.23 (one point two three seconds with no leading mm: ) I get 31:12.00
If I enter incorrectly enter 1:23:45 I get 23:45.00
This will confuse anyone not expecting it! Is there a way of specifying how to interpret the input format?
2) If I have a cell containing a time 01:23.45 and I select the cell, 00:01:23 is displayed in the Formula Bar. If I just click on the formula bar and click the spread sheet again, the value in the cell has changed to 1:23.00 - I have lost the fractions of seconds.
I gather this is because excel is storing it as a fraction, but this is not helpful and is very dangerous – I can corrupt the data without realising it!
How do I stop this happening?
Does anyone know how to get around these two issues?
I am considering storing everything as text in the front page and converting to doubles (i.e. convert to seconds) in the main database.
i.e. I have a standard Worksheet where anyone can input data as text (any format; 1.23, 1:23.45, 1.23.45, etc) and I have a macro to error check, convert to doubles and pull it into the database.
However I then have to convert it back to mm:ss.00 every time I create a table or graph – more complexity in something I’m trying to keep simple and user-friendly.
Sorry if this is partially covered in other threads, but none seem to answer my specific issues.
Paul
I need to find a robust method of entering swim times in the format mm:ss.00 or ss.00 (i.e. 1:23.45 or 12.34).
By ‘robust’ I mean that other people, with little or no knowledge of Excel, also have to be able to enter data into the database without scr*wing it over
I have experimented with the custom format mm:ss.00 but I have the following issues;
1) If I set the format to mm:ss.00 and enter 1:23.45 it stores it correctly.
However if I enter 1.23 (one point two three seconds with no leading mm: ) I get 31:12.00
If I enter incorrectly enter 1:23:45 I get 23:45.00
This will confuse anyone not expecting it! Is there a way of specifying how to interpret the input format?
2) If I have a cell containing a time 01:23.45 and I select the cell, 00:01:23 is displayed in the Formula Bar. If I just click on the formula bar and click the spread sheet again, the value in the cell has changed to 1:23.00 - I have lost the fractions of seconds.
I gather this is because excel is storing it as a fraction, but this is not helpful and is very dangerous – I can corrupt the data without realising it!
How do I stop this happening?
Does anyone know how to get around these two issues?
I am considering storing everything as text in the front page and converting to doubles (i.e. convert to seconds) in the main database.
i.e. I have a standard Worksheet where anyone can input data as text (any format; 1.23, 1:23.45, 1.23.45, etc) and I have a macro to error check, convert to doubles and pull it into the database.
However I then have to convert it back to mm:ss.00 every time I create a table or graph – more complexity in something I’m trying to keep simple and user-friendly.
Sorry if this is partially covered in other threads, but none seem to answer my specific issues.
Paul
