# Help me make a decimal time conversion sheet **URL:** \ **Category:** Factual Questions\ **Created:** [July 12, 2013, 11:29am UTC](https://boards.straightdope.com/t/help-me-make-a-decimal-time-conversion-sheet/663301 "2013-07-12T11:29:48Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Monty](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/monty/32/47_2.png) [@Monty](https://boards.straightdope.com/u/Monty)\ **Post date:** [July 12, 2013, 11:29am UTC](https://boards.straightdope.com/t/help-me-make-a-decimal-time-conversion-sheet/663301/1 "2013-07-12T11:29:48Z")
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. ---
**Author:** ![ZenBeam](https://avatars.discourse-cdn.com/v4/letter/z/3ab097/32.png) [@ZenBeam](https://boards.straightdope.com/u/ZenBeam)\ **Post date:** [July 12, 2013, 11:40am UTC](https://boards.straightdope.com/t/help-me-make-a-decimal-time-conversion-sheet/663301/2 "2013-07-12T11:40:58Z")
> [@Monty](#): > > 1. 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. 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. ---
**Author:** ![Monty](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/monty/32/47_2.png) [@Monty](https://boards.straightdope.com/u/Monty)\ **Post date:** [July 12, 2013, 12:05pm UTC](https://boards.straightdope.com/t/help-me-make-a-decimal-time-conversion-sheet/663301/3 "2013-07-12T12:05:23Z")
I think so. But I’m not worried about that. I just want a comprehensive conversion. ---
**Author:** ![naita](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/naita/32/5862_2.png) [@naita](https://boards.straightdope.com/u/naita)\ **Post date:** [July 12, 2013, 12:22pm UTC](https://boards.straightdope.com/t/help-me-make-a-decimal-time-conversion-sheet/663301/4 "2013-07-12T12:22:51Z")
Here’s my page using javascript to do almost exactly what you’re asking for, just copy the calculations to excel: [http://www.tuftin.net/~bjornar/NoWhere/converttime.html](http://www.tuftin.net/~bjornar/NoWhere/converttime.html) > [@](#): > > You start with time on the form hh:mm:ss.t and a timezone. First convert to GMT. Then do a simple: > hh \* 125/3 + mm \* 25/36 + ss.t \* 5/432 > > To go the other way, first divide by 125/3 for the number of hours, divide the remainder by 25/36 for the number of minutes, and divide that remainder again by 5/432 for the number of seconds. ---
**Author:** ![Monty](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/monty/32/47_2.png) [@Monty](https://boards.straightdope.com/u/Monty)\ **Post date:** [July 12, 2013, 1:10pm UTC](https://boards.straightdope.com/t/help-me-make-a-decimal-time-conversion-sheet/663301/5 "2013-07-12T13:10:57Z")
**naita** : Thanks! ---
**Author:** ![Monty](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/monty/32/47_2.png) [@Monty](https://boards.straightdope.com/u/Monty)\ **Post date:** [July 12, 2013, 1:20pm UTC](https://boards.straightdope.com/t/help-me-make-a-decimal-time-conversion-sheet/663301/6 "2013-07-12T13:20:25Z")
By the way, **naita** , I’m using a pretty sweet app on my android tablet for decimal time. It’s called LogiClock. ---
**Author:** ![Ximenean](https://avatars.discourse-cdn.com/v4/letter/x/aca169/32.png) [@Ximenean](https://boards.straightdope.com/u/Ximenean)\ **Post date:** [July 12, 2013, 1:52pm UTC](https://boards.straightdope.com/t/help-me-make-a-decimal-time-conversion-sheet/663301/7 "2013-07-12T13:52:06Z")
> [@Monty](#): > > 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. 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. ---
**Author:** ![Monty](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/monty/32/47_2.png) [@Monty](https://boards.straightdope.com/u/Monty)\ **Post date:** [July 12, 2013, 2:40pm UTC](https://boards.straightdope.com/t/help-me-make-a-decimal-time-conversion-sheet/663301/8 "2013-07-12T14:40:08Z")
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. ---
**Author:** ![OldGuy](https://avatars.discourse-cdn.com/v4/letter/o/3bc359/32.png) [@OldGuy](https://boards.straightdope.com/u/OldGuy)\ **Post date:** [July 12, 2013, 5:04pm UTC](https://boards.straightdope.com/t/help-me-make-a-decimal-time-conversion-sheet/663301/9 "2013-07-12T17:04:49Z")
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. ---
**Author:** ![naita](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/naita/32/5862_2.png) [@naita](https://boards.straightdope.com/u/naita)\ **Post date:** [July 12, 2013, 5:48pm UTC](https://boards.straightdope.com/t/help-me-make-a-decimal-time-conversion-sheet/663301/10 "2013-07-12T17:48:52Z")
> [@Ximenean](#): > > 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. 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. ---
**Author:** ![naita](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/naita/32/5862_2.png) [@naita](https://boards.straightdope.com/u/naita)\ **Post date:** [July 12, 2013, 5:52pm UTC](https://boards.straightdope.com/t/help-me-make-a-decimal-time-conversion-sheet/663301/11 "2013-07-12T17:52:51Z")
> [@Monty](#): > > By the way, **naita** , I’m using a pretty sweet app on my android tablet for decimal time. It’s called LogiClock. 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!