Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bengt Bergholm
 
Posts: n/a
Default 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   Report Post  
Mangesh Yadav
 
Posts: n/a
Default

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   Report Post  
Bengt Bergholm
 
Posts: n/a
Default

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






Attached Images
 
  #5   Report Post  
Mangesh Yadav
 
Posts: n/a
Default

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   Report Post  
Bengt Bergholm
 
Posts: n/a
Default

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   Report Post  
Mangesh Yadav
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional formatting with time values Access Idiot Excel Discussion (Misc queries) 2 September 13th 05 03:29 PM
How do I convert time (2:30) to a number (2.5) in a formula? tammyj Excel Worksheet Functions 1 August 25th 05 04:46 PM
subtraction of times, convert & multiply by a conditioned rate sanscull Excel Worksheet Functions 6 May 20th 05 02:02 AM
In Excel how can you convert a cell formatted for time (4:30) to . Lisa Excel Worksheet Functions 1 February 15th 05 01:06 PM
Convert text file to MS_Excel Aqua Flow Excel Discussion (Misc queries) 1 November 30th 04 03:55 AM


All times are GMT +1. The time now is 09:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"