I use excel spreadsheet for entering my time worked. I have it currently set
to add up hours worked, etc. Is there a formula I can enter to have excel
convert from time format to tenths. For example, if I work 8 hours and 12
minutes, I want it to convert it to 8.2 hours. We use the conversion chart
below. Any suggestions?

1-6 minutes = .1
7-12 minutes = .2
13-18 = .3
19-24 = .4
25-30 = .5
31-36 = .6
37-42 = .7
43-48 = .8
49-54 = .9
55-60 = 1.0

dwhapp wrote:
I use excel spreadsheet for entering my time worked. I have it currently set
to add up hours worked, etc. Is there a formula I can enter to have excel
convert from time format to tenths. For example, if I work 8 hours and 12
minutes, I want it to convert it to 8.2 hours. We use the conversion chart
below. Any suggestions?

1-6 minutes = .1
7-12 minutes = .2
13-18 = .3
19-24 = .4
25-30 = .5
31-36 = .6
37-42 = .7
43-48 = .8
49-54 = .9
55-60 = 1.0


=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)

=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)

That returns incorrect results. The OP's conversion table starts at 1 minute
so does that mean a time like 11:00 PM should return 23.0 ?

With the above formula:

11:00 PM = 23.1
9:54 AM = 10.0
9:42 PM = 21.8
4:12 AM = 4.3

And here's a weird one:

1:06 PM = 1.1
11:06 PM = 23.2

Both have the same minute yet return a different decimal.

This seems to work:

=INT(A1*24)+CEILING(MINUTE(A1)/60,0.1)

Format as General or Number

0 minutes doesn't get rounded.

11:00 PM = 23.0

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
dwhapp wrote:
I use excel spreadsheet for entering my time worked. I have it currently
set to add up hours worked, etc. Is there a formula I can enter to have
excel convert from time format to tenths. For example, if I work 8 hours
and 12 minutes, I want it to convert it to 8.2 hours. We use the
conversion chart below. Any suggestions?

1-6 minutes = .1
7-12 minutes = .2
13-18 = .3
19-24 = .4
25-30 = .5
31-36 = .6
37-42 = .7
43-48 = .8
49-54 = .9
55-60 = 1.0


=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)

Typo:

And here's a weird one:
1:06 PM = 1.1

Should be:

1:06 PM = 13.1


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)

That returns incorrect results. The OP's conversion table starts at 1
minute so does that mean a time like 11:00 PM should return 23.0 ?

With the above formula:

11:00 PM = 23.1
9:54 AM = 10.0
9:42 PM = 21.8
4:12 AM = 4.3

And here's a weird one:

1:06 PM = 1.1
11:06 PM = 23.2

Both have the same minute yet return a different decimal.

This seems to work:

=INT(A1*24)+CEILING(MINUTE(A1)/60,0.1)

Format as General or Number

0 minutes doesn't get rounded.

11:00 PM = 23.0

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
dwhapp wrote:
I use excel spreadsheet for entering my time worked. I have it
currently set to add up hours worked, etc. Is there a formula I can
enter to have excel convert from time format to tenths. For example, if
I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We
use the conversion chart below. Any suggestions?

1-6 minutes = .1
7-12 minutes = .2
13-18 = .3
19-24 = .4
25-30 = .5
31-36 = .6
37-42 = .7
43-48 = .8
49-54 = .9
55-60 = 1.0


=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)

=INT(A1*24)+CEILING(MINUTE(A1)/60,0.1)

We can reduce it by one calculation cycle by replacing INT:

=HOUR(A1)+CEILING(MINUTE(A1)/60,0.1)

I wonder why I don't see these things the first time around!


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)

That returns incorrect results. The OP's conversion table starts at 1
minute so does that mean a time like 11:00 PM should return 23.0 ?

With the above formula:

11:00 PM = 23.1
9:54 AM = 10.0
9:42 PM = 21.8
4:12 AM = 4.3

And here's a weird one:

1:06 PM = 1.1
11:06 PM = 23.2

Both have the same minute yet return a different decimal.

This seems to work:

=INT(A1*24)+CEILING(MINUTE(A1)/60,0.1)

Format as General or Number

0 minutes doesn't get rounded.

11:00 PM = 23.0

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
dwhapp wrote:
I use excel spreadsheet for entering my time worked. I have it
currently set to add up hours worked, etc. Is there a formula I can
enter to have excel convert from time format to tenths. For example, if
I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We
use the conversion chart below. Any suggestions?

1-6 minutes = .1
7-12 minutes = .2
13-18 = .3
19-24 = .4
25-30 = .5
31-36 = .6
37-42 = .7
43-48 = .8
49-54 = .9
55-60 = 1.0


=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)

I'm not entirely convinced the OP has the hours and minutes bundled up into
a time value (rather, I'm thinking the hours are in one cell and the minutes
in another). The reason I suspect this is the chart the OP says he uses
starts a 1 minute and ends at 60 minutes and, of course, no time value would
have 60 minutes in it (it would have the 0 minutes missing from the chart).

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)

That returns incorrect results. The OP's conversion table starts at 1
minute so does that mean a time like 11:00 PM should return 23.0 ?

With the above formula:

11:00 PM = 23.1
9:54 AM = 10.0
9:42 PM = 21.8
4:12 AM = 4.3

And here's a weird one:

1:06 PM = 1.1
11:06 PM = 23.2

Both have the same minute yet return a different decimal.

This seems to work:

=INT(A1*24)+CEILING(MINUTE(A1)/60,0.1)

Format as General or Number

0 minutes doesn't get rounded.

11:00 PM = 23.0

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
dwhapp wrote:
I use excel spreadsheet for entering my time worked. I have it
currently set to add up hours worked, etc. Is there a formula I can
enter to have excel convert from time format to tenths. For example, if
I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We
use the conversion chart below. Any suggestions?

1-6 minutes = .1
7-12 minutes = .2
13-18 = .3
19-24 = .4
25-30 = .5
31-36 = .6
37-42 = .7
43-48 = .8
49-54 = .9
55-60 = 1.0


=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)

Hmmm...

have excel convert from time format

I read that as entering a time. 8:12

On the conversion table, if a time is =55 minutes it gets rounded up to the
next whole hour so a time with 0 minutes should remain at 0 minutes/tenths.
At least, that's my take!

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
I'm not entirely convinced the OP has the hours and minutes bundled up
into a time value (rather, I'm thinking the hours are in one cell and the
minutes in another). The reason I suspect this is the chart the OP says he
uses starts a 1 minute and ends at 60 minutes and, of course, no time
value would have 60 minutes in it (it would have the 0 minutes missing
from the chart).

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)

That returns incorrect results. The OP's conversion table starts at 1
minute so does that mean a time like 11:00 PM should return 23.0 ?

With the above formula:

11:00 PM = 23.1
9:54 AM = 10.0
9:42 PM = 21.8
4:12 AM = 4.3

And here's a weird one:

1:06 PM = 1.1
11:06 PM = 23.2

Both have the same minute yet return a different decimal.

This seems to work:

=INT(A1*24)+CEILING(MINUTE(A1)/60,0.1)

Format as General or Number

0 minutes doesn't get rounded.

11:00 PM = 23.0

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
dwhapp wrote:
I use excel spreadsheet for entering my time worked. I have it
currently set to add up hours worked, etc. Is there a formula I can
enter to have excel convert from time format to tenths. For example,
if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours.
We use the conversion chart below. Any suggestions?

1-6 minutes = .1
7-12 minutes = .2
13-18 = .3
19-24 = .4
25-30 = .5
31-36 = .6
37-42 = .7
43-48 = .8
49-54 = .9
55-60 = 1.0


=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)

I sort of read of that quickly and took it to mean the OP knew how to form a
time value (from the hours and minutes) but didn't know how to apply his
chart to it. But in re-reading it more carefully, I'm now thinking you are
right in your interpretation. Assuming you are, here is a formula using my
ROUNDUP approach (obviously, very similar in approach to your CEILING
formula)...

=HOUR(A1)+ROUNDUP(MINUTE(A1)/60,1)

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Hmmm...

have excel convert from time format

I read that as entering a time. 8:12

On the conversion table, if a time is =55 minutes it gets rounded up to
the next whole hour so a time with 0 minutes should remain at 0
minutes/tenths. At least, that's my take!

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
I'm not entirely convinced the OP has the hours and minutes bundled up
into a time value (rather, I'm thinking the hours are in one cell and the
minutes in another). The reason I suspect this is the chart the OP says
he uses starts a 1 minute and ends at 60 minutes and, of course, no time
value would have 60 minutes in it (it would have the 0 minutes missing
from the chart).

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)

That returns incorrect results. The OP's conversion table starts at 1
minute so does that mean a time like 11:00 PM should return 23.0 ?

With the above formula:

11:00 PM = 23.1
9:54 AM = 10.0
9:42 PM = 21.8
4:12 AM = 4.3

And here's a weird one:

1:06 PM = 1.1
11:06 PM = 23.2

Both have the same minute yet return a different decimal.

This seems to work:

=INT(A1*24)+CEILING(MINUTE(A1)/60,0.1)

Format as General or Number

0 minutes doesn't get rounded.

11:00 PM = 23.0

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
dwhapp wrote:
I use excel spreadsheet for entering my time worked. I have it
currently set to add up hours worked, etc. Is there a formula I can
enter to have excel convert from time format to tenths. For example,
if I work 8 hours and 12 minutes, I want it to convert it to 8.2
hours. We use the conversion chart below. Any suggestions?

1-6 minutes = .1
7-12 minutes = .2
13-18 = .3
19-24 = .4
25-30 = .5
31-36 = .6
37-42 = .7
43-48 = .8
49-54 = .9
55-60 = 1.0


=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)

T. Valko wrote:
=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)

That returns incorrect results. The OP's conversion table starts at 1 minute
so does that mean a time like 11:00 PM should return 23.0 ?

With the above formula:

11:00 PM = 23.1
9:54 AM = 10.0
9:42 PM = 21.8
4:12 AM = 4.3

And here's a weird one:

1:06 PM = 1.1
11:06 PM = 23.2



You're right, I tested this on a limited sample and didn't notice any
discrepancies. Check out these results!!!

12:59 PM 13.0
1:00 PM 12.9
1:01 PM 13.1

In case this helps any, there is a formula that can be used to replace your
chart. For the result as text, this...

=TEXT(ROUNDUP(A1/60,1),".0")

And for the result as a number, this...

=--TEXT(ROUNDUP(A1/60,1),".0")

where A1 is assumed to contain the number of minutes from 1 to 60.

--
Rick (MVP - Excel)


"dwhapp" wrote in message
...
I use excel spreadsheet for entering my time worked. I have it currently
set
to add up hours worked, etc. Is there a formula I can enter to have excel
convert from time format to tenths. For example, if I work 8 hours and 12
minutes, I want it to convert it to 8.2 hours. We use the conversion
chart
below. Any suggestions?

1-6 minutes = .1
7-12 minutes = .2
13-18 = .3
19-24 = .4
25-30 = .5
31-36 = .6
37-42 = .7
43-48 = .8
49-54 = .9
55-60 = 1.0

Try this:

=ROUNDUP(A1*24,1)


"dwhapp" wrote:

I use excel spreadsheet for entering my time worked. I have it currently set
to add up hours worked, etc. Is there a formula I can enter to have excel
convert from time format to tenths. For example, if I work 8 hours and 12
minutes, I want it to convert it to 8.2 hours. We use the conversion chart
below. Any suggestions?

1-6 minutes = .1
7-12 minutes = .2
13-18 = .3
19-24 = .4
25-30 = .5
31-36 = .6
37-42 = .7
43-48 = .8
49-54 = .9
55-60 = 1.0