Help me make a decimal time conversion sheet

Well, this shouldn’t be as hard as it apparently is for me, so I’m coming to the TM’s math/spreadsheet gurus for some assistance. Here’s what I’m trying to do:

[ol][li]Make an Excel (or Excel clone) workbook with two worksheets, one for converting known decimal time to standard time (for convenience, using a 24-hour/military format) and the other for converting standard time to decimal time.[/li][li]Each type of time will give hour, minute, second.[/li][li]Each worksheet will be populated with all the possible times. For standard time, this will go from 00:00:00 to 23:59:59. For decimal time, this will go from 0:00:00 to 9:99:99.[/ol][/li]
Explanation of decimal time as used for this workbook:

[list=a][li]The day is divided into ten hours.[/li][li]The hour is divided into 100 minutes.[/li][li]The minute is divided into 100 seconds.[/list][/li]
I realize that there is obviously not a one-to-one correspondence between the two time systems so I’m not worried about two different times in one system having the same resulting time in the other system. What I’d like to know is how to have the program generate both the times mentioned in Item #3 and the formula for conversion to the other system.

Please let me know if I wasn’t clear enough.

Does this mean you’ll have 86,400 rows in the standard time sheet, and 100,000 rows in the decimal time sheet? That seems like a lot.

I think so. But I’m not worried about that. I just want a comprehensive conversion.

Here’s my page using javascript to do almost exactly what you’re asking for, just copy the calculations to excel:

naita: Thanks!

By the way, naita, I’m using a pretty sweet app on my android tablet for decimal time. It’s called LogiClock.

I may be misunderstanding you here, but it seems to me that there would be a one-to-one correspondence, since any particular time has just one representation in either system.

Nope. A “decimal minute” is not the same length of time as a “standard minute”. Since the time periods in the two systems are of different durations, there is some overlap when converting times. Consider the numbers in ZenBeam’s question in post #2.

Assuming you want a day to be the same in both formats, this is quite easy. A 24-hour time that is displayed in excel as hh:mm:ss.t is actually just a number ranging from 0 for 00:00:00.0 to 1 for 24:00:00.0. You can see this by typing a number and then changing the format of the cell to “general.”

Therefore, to get decimal time in B1 from 24 hour time in A1 simply put “=A1” into B1 and format it as “general.” The first decimal digit is then the decimal hour, then next two are the minute and the last are the second and fractions of seconds.

If you want it to have the particular format with :'s in it, I think you’re going to have to manipulate it as a text string after extracting the relevant digits.

With infinite precision, or one system with more precision than the other, yes. With fixed precision, no. Compare it to converting between Celsius and Fahrenheit if you’re only going to use whole numbers.

I don’t use it for anything that’s so precise I can’t just do it in my head. (Also, I don’t have anything running android.)

I remember the basic 8ths of a thousand

3 am = 125
6 am = 250
9 am = 375
noon = 500

and so on, and I know a regular hour is 41 2/3 decimal minutes. Throw in some basic addition, multiplication and division and hey presto!