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

A1 contains your time, the use:

=ROUNDUP(A1*24*10,0)/10


Mangesh





"Bengt Bergholm" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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
>
>
>

Mangesh,

Do you by any chance use semicolons as separators in formulas instead of
commas?

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Bengt Bergholm" <[email protected]> wrote in message
news:%23mndk%[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>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
>>
>>
>>
>
>


--------------------------------------------------------------------------------

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" <[email protected]> wrote in message
news:#mndk#[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> >
> >>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
> >
> >
> >
>
>


----------------------------------------------------------------------------
----

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
>
>

As long as it works, well and good.

Mangesh




"Bengt Bergholm" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >