How to convert time into "block time"?

[Bengt Bergholm]

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

A1 contains your time, the use:

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


Mangesh

 

[Sandy Mann]

Mangesh,

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

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

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]

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]

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]

As long as it works, well and good.

Mangesh