Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to convert time into "block time"?
Sorry for my lack of terminology here but I need help to accomplish the
following: From cells containing time in format 0:12, 1:18 etc, meaning 12 minutes and one hour and 18 minutes converting it according to: 1-6 minutes = 0.1 7-12 minutes = 0.2 13-18 minutes = 0.3 19-24 minutes = 0.4 25-30 minutes = 0.5 31-36 minutes = 0.6 37-42 minutes = 0.7 43-48 minutes = 0.8 49-54 minutes = 0.9 55-60 minutes = 1.0 61-66 minutes = 1.1 etc etc... That is, rounding and displaying as 10th's of the hour depending on how many minutes there are in the input cell. Any ready-to-use formula or vba-script would be greatly appreciated! :) BoB |
#2
|
|||
|
|||
A1 contains your time, the use:
=ROUNDUP(A1*24*10,0)/10 Mangesh "Bengt Bergholm" wrote in message ... Sorry for my lack of terminology here but I need help to accomplish the following: From cells containing time in format 0:12, 1:18 etc, meaning 12 minutes and one hour and 18 minutes converting it according to: 1-6 minutes = 0.1 7-12 minutes = 0.2 13-18 minutes = 0.3 19-24 minutes = 0.4 25-30 minutes = 0.5 31-36 minutes = 0.6 37-42 minutes = 0.7 43-48 minutes = 0.8 49-54 minutes = 0.9 55-60 minutes = 1.0 61-66 minutes = 1.1 etc etc... That is, rounding and displaying as 10th's of the hour depending on how many minutes there are in the input cell. Any ready-to-use formula or vba-script would be greatly appreciated! :) BoB |
#3
|
|||
|
|||
Thanks for the formular Mangesh, unfortunatley it doesn't work, Excel
complains about som error in the formula when I try to adapt it (standard formula error mess). After OK'ing the dialog it highlights the 10,0 part. See attached screenshot. BoB Mangesh Yadav wrote: A1 contains your time, the use: =ROUNDUP(A1*24*10,0)/10 Mangesh "Bengt Bergholm" wrote in message ... Sorry for my lack of terminology here but I need help to accomplish the following: From cells containing time in format 0:12, 1:18 etc, meaning 12 minutes and one hour and 18 minutes converting it according to: 1-6 minutes = 0.1 7-12 minutes = 0.2 13-18 minutes = 0.3 19-24 minutes = 0.4 25-30 minutes = 0.5 31-36 minutes = 0.6 37-42 minutes = 0.7 43-48 minutes = 0.8 49-54 minutes = 0.9 55-60 minutes = 1.0 61-66 minutes = 1.1 etc etc... That is, rounding and displaying as 10th's of the hour depending on how many minutes there are in the input cell. Any ready-to-use formula or vba-script would be greatly appreciated! :) BoB |
#4
|
|||
|
|||
|
#5
|
|||
|
|||
Hi Bengt,
Your system requires you to use the semi-colon (;) instead of a comma (,) as can be seen from your screen shot. So in my formula replace the comma with a semi-colon, something like this: =ROUNDUP(A1*24*10;0)/10 Mangesh "Bengt Bergholm" wrote in message ... Thanks for the formular Mangesh, unfortunatley it doesn't work, Excel complains about som error in the formula when I try to adapt it (standard formula error mess). After OK'ing the dialog it highlights the 10,0 part. See attached screenshot. BoB Mangesh Yadav wrote: A1 contains your time, the use: =ROUNDUP(A1*24*10,0)/10 Mangesh "Bengt Bergholm" wrote in message ... Sorry for my lack of terminology here but I need help to accomplish the following: From cells containing time in format 0:12, 1:18 etc, meaning 12 minutes and one hour and 18 minutes converting it according to: 1-6 minutes = 0.1 7-12 minutes = 0.2 13-18 minutes = 0.3 19-24 minutes = 0.4 25-30 minutes = 0.5 31-36 minutes = 0.6 37-42 minutes = 0.7 43-48 minutes = 0.8 49-54 minutes = 0.9 55-60 minutes = 1.0 61-66 minutes = 1.1 etc etc... That is, rounding and displaying as 10th's of the hour depending on how many minutes there are in the input cell. Any ready-to-use formula or vba-script would be greatly appreciated! :) BoB ---------------------------------------------------------------------------- ---- |
#6
|
|||
|
|||
You're right, it works with a semi-colon, strange though, I tested that
already since I know of this delimiter problem but it didn't work then but now it does... Thanks! Mangesh Yadav wrote: Hi Bengt, Your system requires you to use the semi-colon (;) instead of a comma (,) as can be seen from your screen shot. So in my formula replace the comma with a semi-colon, something like this: =ROUNDUP(A1*24*10;0)/10 Mangesh |
#7
|
|||
|
|||
As long as it works, well and good. :)
Mangesh "Bengt Bergholm" wrote in message ... You're right, it works with a semi-colon, strange though, I tested that already since I know of this delimiter problem but it didn't work then but now it does... Thanks! Mangesh Yadav wrote: Hi Bengt, Your system requires you to use the semi-colon (;) instead of a comma (,) as can be seen from your screen shot. So in my formula replace the comma with a semi-colon, something like this: =ROUNDUP(A1*24*10;0)/10 Mangesh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting with time values | Excel Discussion (Misc queries) | |||
How do I convert time (2:30) to a number (2.5) in a formula? | Excel Worksheet Functions | |||
subtraction of times, convert & multiply by a conditioned rate | Excel Worksheet Functions | |||
In Excel how can you convert a cell formatted for time (4:30) to . | Excel Worksheet Functions | |||
Convert text file to MS_Excel | Excel Discussion (Misc queries) |