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

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

Mangesh,

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

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"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







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

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







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

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