Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing decimal time into 24 hour time and reverse
Is it possible to change decimal time into 24hour time? For example, 1.75
hours in decimal in CELL A1 into 1:45 in 24 hour time in CELL B1. Also - how would I reverse it? For example 2:30 24 hour time into 2.5 decimal time. Also note that the decimal time would need to increment above 24:00 as the totals will be above this value Best Regards and thanks for looking. Bobzter |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing decimal time into 24 hour time and reverse
bobzter100,
In cell B1, enter this formula: =A1/24 ....then format then give the cell the format "[h]:mm:ss;@" (that format is the same thing as Format Cells dialog Number tab Category box "Time" category Type: box 37:30:55) to go in revers, just multiply by 24 and format the cell as a number with decimal places. HTH, Conan "Bobzter100" wrote in message ... Is it possible to change decimal time into 24hour time? For example, 1.75 hours in decimal in CELL A1 into 1:45 in 24 hour time in CELL B1. Also - how would I reverse it? For example 2:30 24 hour time into 2.5 decimal time. Also note that the decimal time would need to increment above 24:00 as the totals will be above this value Best Regards and thanks for looking. Bobzter |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing decimal time into 24 hour time and reverse
Hi Conan
Many thanks - that has provided me with a solution to what i thought was my only problem! Any advice on the folowing concerning time? I have entry cells formatted to [H]:mm for user input. This allows a user to input any time, including a time greater than 24:00 hours. However, users being users they tend not to enter what you want so in many instances they enter, for example, 74.5 hours in decimal which shows up as 1788. Is there any way i can use the adjacent column to convert this value into the correct value of 74.5 (24 hour time)? Note that i've tried to restrict user input by using validation through validate\time\between\ but this will only alow a time span of 00:00 - 24:00 and essentially the user can enter any amout of hours with no upper limit. Best regards Bobzter "Conan Kelly" wrote: bobzter100, In cell B1, enter this formula: =A1/24 ....then format then give the cell the format "[h]:mm:ss;@" (that format is the same thing as Format Cells dialog Number tab Category box "Time" category Type: box 37:30:55) to go in revers, just multiply by 24 and format the cell as a number with decimal places. HTH, Conan "Bobzter100" wrote in message ... Is it possible to change decimal time into 24hour time? For example, 1.75 hours in decimal in CELL A1 into 1:45 in 24 hour time in CELL B1. Also - how would I reverse it? For example 2:30 24 hour time into 2.5 decimal time. Also note that the decimal time would need to increment above 24:00 as the totals will be above this value Best Regards and thanks for looking. Bobzter |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing decimal time into 24 hour time and reverse
You might find this to be useful:
http://www.cpearson.com/excel/DateTimeEntry.htm It enables you/your users to enter times without the colon. Hope this helps. Pete On Jan 25, 10:44*am, Bobzter100 wrote: Hi Conan Many thanks - that has provided me with a solution to what i thought was my only problem! Any advice on the folowing concerning time? I have entry cells formatted to [H]:mm for user input. This allows a user to input any time, including a time greater than 24:00 hours. However, users being users they tend not to enter what you want so in many instances they enter, for example, 74.5 hours in decimal which shows up as 1788. Is there any way i can use the adjacent column to convert this value into the correct value of 74.5 (24 hour time)? Note that i've tried to restrict user input by using validation through validate\time\between\ but this will only alow a time span of 00:00 - 24:00 and essentially the user can enter any amout of hours with no upper limit. Best regards Bobzter "Conan Kelly" wrote: bobzter100, In cell B1, enter this formula: * * =A1/24 ....then format then give the cell the format "[h]:mm:ss;@" (that format is the same thing as Format Cells dialog Number tab Category box "Time" category Type: box 37:30:55) to go in revers, just multiply by 24 and format the cell as a number with decimal places. HTH, Conan "Bobzter100" wrote in message ... Is it possible to change decimal time into 24hour time? For example, 1..75 hours in decimal in CELL A1 into 1:45 in 24 hour time in CELL B1. Also - how would I reverse it? For example 2:30 24 hour time into 2.5 decimal time. Also note that the decimal time would need to increment above 24:00 as the totals will be above this value Best Regards and thanks for looking. Bobzter- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing decimal time into 24 hour time and reverse
If you want to convert from hours to Excel time, divide by 24 (as Excel
times are counted in days). To prompt the users to use the right format when inputting the data, use the Input message part of Data Validation, with a message such as: Input hours and minutes in hh:mm format -- David Biddulph "Bobzter100" wrote in message ... Hi Conan Many thanks - that has provided me with a solution to what i thought was my only problem! Any advice on the folowing concerning time? I have entry cells formatted to [H]:mm for user input. This allows a user to input any time, including a time greater than 24:00 hours. However, users being users they tend not to enter what you want so in many instances they enter, for example, 74.5 hours in decimal which shows up as 1788. Is there any way i can use the adjacent column to convert this value into the correct value of 74.5 (24 hour time)? Note that i've tried to restrict user input by using validation through validate\time\between\ but this will only alow a time span of 00:00 - 24:00 and essentially the user can enter any amout of hours with no upper limit. Best regards Bobzter "Conan Kelly" wrote: bobzter100, In cell B1, enter this formula: =A1/24 ....then format then give the cell the format "[h]:mm:ss;@" (that format is the same thing as Format Cells dialog Number tab Category box "Time" category Type: box 37:30:55) to go in revers, just multiply by 24 and format the cell as a number with decimal places. HTH, Conan "Bobzter100" wrote in message ... Is it possible to change decimal time into 24hour time? For example, 1.75 hours in decimal in CELL A1 into 1:45 in 24 hour time in CELL B1. Also - how would I reverse it? For example 2:30 24 hour time into 2.5 decimal time. Also note that the decimal time would need to increment above 24:00 as the totals will be above this value Best Regards and thanks for looking. Bobzter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to calculate time start & time finish in quarter hour | Excel Discussion (Misc queries) | |||
Converting decimal time to standard time? | Excel Discussion (Misc queries) | |||
convert decimal numbers to a fraction of an hour for payroll hour | Excel Worksheet Functions | |||
making a time a measurement of time, not an hour of the day?? | Excel Discussion (Misc queries) | |||
Convert decimal hour into time format? | Excel Discussion (Misc queries) |