Converting time to decimal format
 

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

Converting time to decimal format
 

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)

Converting time to decimal format
 

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

Converting time to decimal format
 

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

Converting time to decimal format
 

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)

Converting time to decimal format
 

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

Converting time to decimal format
 

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)

Converting time to decimal format
 

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)

Converting time to decimal format
 

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)

Converting time to decimal format
 

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

Converting time to decimal format
 

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

Converting time to decimal format
 

Maybe I should explain my question better. Suppose I enter my start time
8:45 and end time 17:00. Excel will return a total time worked for the day as
8:15. If I work another day from 8:00 to 17:45 then it will give me 8:45.
Once I get the time worked for the day, using the chart I already gave, I
want excel to convert it to decimal format. So in my first example it should
convert it to 8.3 hours and the second example it should convert it to 8.8
hours. It's true that if I work 8 hours and 55 minutes then it should give
me 9.0 hours.

"Rick Rothstein" wrote:

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)

Converting time to decimal format
 

So what answers do you get from Rick's formula with your data? For me, his
formula gives 8.3, 8.8, and 9.0 in the cases where you wanted 8.3, 8.8, and
9.0. I therefore fail to see your problem.
--
David Biddulph

"dwhapp" wrote in message
...
Maybe I should explain my question better. Suppose I enter my start time
8:45 and end time 17:00. Excel will return a total time worked for the day
as
8:15. If I work another day from 8:00 to 17:45 then it will give me 8:45.
Once I get the time worked for the day, using the chart I already gave, I
want excel to convert it to decimal format. So in my first example it
should
convert it to 8.3 hours and the second example it should convert it to 8.8
hours. It's true that if I work 8 hours and 55 minutes then it should
give
me 9.0 hours.

"Rick Rothstein" wrote:

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)

Converting time to decimal format
 

Assuming your Start Time (8:45) is in A1 and your End Time (17:00) is in B1,
then you would use either of these two formulas to calculate the difference
and apply your chart at the same time...

Rick's Formula
========================
=HOUR(B1-A1)+ROUNDUP(MINUTE(B1-A1)/60,1)

Biff's Formula
========================
=HOUR(B1-A1)+CEILING(MINUTE(B1-A1)/60,0.1)

One note though... the values in the calculated column are what you see, so
when you add them up, you are adding up the rounded values, not the actually
calculated differences. So, for your example, the 8 hours 15 minutes
difference, which actually calculates to 8.25, will be added as 8.3 (not
8.25) when summed up.

--
Rick (MVP - Excel)


"dwhapp" wrote in message
...
Maybe I should explain my question better. Suppose I enter my start time
8:45 and end time 17:00. Excel will return a total time worked for the day
as
8:15. If I work another day from 8:00 to 17:45 then it will give me 8:45.
Once I get the time worked for the day, using the chart I already gave, I
want excel to convert it to decimal format. So in my first example it
should
convert it to 8.3 hours and the second example it should convert it to 8.8
hours. It's true that if I work 8 hours and 55 minutes then it should
give
me 9.0 hours.

"Rick Rothstein" wrote:

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)

Converting time to decimal format
 

Try this:

If your end times will never span past midnight:

A1 = start time = 8:45
B1 = end time = 17:00

=IF(COUNT(A1:B1)<2,"",CEILING((B1-A1)*24,0.1))

If your end times might span past midnight:

A1 = start time = 23:00
B1 = end time = 7:15

=IF(COUNT(A1:B1)<2,"",CEILING(MOD(B1-A1,1)*24,0.1))

--
Biff
Microsoft Excel MVP


"dwhapp" wrote in message
...
Maybe I should explain my question better. Suppose I enter my start time
8:45 and end time 17:00. Excel will return a total time worked for the day
as
8:15. If I work another day from 8:00 to 17:45 then it will give me 8:45.
Once I get the time worked for the day, using the chart I already gave, I
want excel to convert it to decimal format. So in my first example it
should
convert it to 8.3 hours and the second example it should convert it to 8.8
hours. It's true that if I work 8 hours and 55 minutes then it should
give
me 9.0 hours.

"Rick Rothstein" wrote:

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)

Converting time to decimal format
 

Im assuming the B1-A1 reference concerns the end time minus the start time or
am I wrong. Finally, what happens if I add in two additional boxes for lunch
start time and lunch end time whereas:

A1 B1 C1 D1
start lunch start lunch end end
8:00 12:00 12:30 17:15

Currently I have the formula set at =(B1-A1)+(D1-C1) to give me the total
time worked for the day.


"Rick Rothstein" wrote:

Assuming your Start Time (8:45) is in A1 and your End Time (17:00) is in B1,
then you would use either of these two formulas to calculate the difference
and apply your chart at the same time...

Rick's Formula
========================
=HOUR(B1-A1)+ROUNDUP(MINUTE(B1-A1)/60,1)

Biff's Formula
========================
=HOUR(B1-A1)+CEILING(MINUTE(B1-A1)/60,0.1)

One note though... the values in the calculated column are what you see, so
when you add them up, you are adding up the rounded values, not the actually
calculated differences. So, for your example, the 8 hours 15 minutes
difference, which actually calculates to 8.25, will be added as 8.3 (not
8.25) when summed up.

--
Rick (MVP - Excel)


"dwhapp" wrote in message
...
Maybe I should explain my question better. Suppose I enter my start time
8:45 and end time 17:00. Excel will return a total time worked for the day
as
8:15. If I work another day from 8:00 to 17:45 then it will give me 8:45.
Once I get the time worked for the day, using the chart I already gave, I
want excel to convert it to decimal format. So in my first example it
should
convert it to 8.3 hours and the second example it should convert it to 8.8
hours. It's true that if I work 8 hours and 55 minutes then it should
give
me 9.0 hours.

"Rick Rothstein" wrote:

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)

Converting time to decimal format
 

Just put that formula call into the two places I put the simpler formula in
earlier...

Rick's Formula
========================
=HOUR(B1-A1+D1-C1)+ROUNDUP(MINUTE(B1-A1+D1-C1)/60,1)

Biff's Formula
========================
=HOUR(B1-A1+D1-C1)+CEILING(MINUTE(B1-A1+D1-C1)/60,0.1)

--
Rick (MVP - Excel)


"dwhapp" wrote in message
...
Im assuming the B1-A1 reference concerns the end time minus the start time
or
am I wrong. Finally, what happens if I add in two additional boxes for
lunch
start time and lunch end time whereas:

A1 B1 C1 D1
start lunch start lunch end end
8:00 12:00 12:30 17:15

Currently I have the formula set at =(B1-A1)+(D1-C1) to give me the total
time worked for the day.


"Rick Rothstein" wrote:

Assuming your Start Time (8:45) is in A1 and your End Time (17:00) is in
B1,
then you would use either of these two formulas to calculate the
difference
and apply your chart at the same time...

Rick's Formula
========================
=HOUR(B1-A1)+ROUNDUP(MINUTE(B1-A1)/60,1)

Biff's Formula
========================
=HOUR(B1-A1)+CEILING(MINUTE(B1-A1)/60,0.1)

One note though... the values in the calculated column are what you see,
so
when you add them up, you are adding up the rounded values, not the
actually
calculated differences. So, for your example, the 8 hours 15 minutes
difference, which actually calculates to 8.25, will be added as 8.3 (not
8.25) when summed up.

--
Rick (MVP - Excel)


"dwhapp" wrote in message
...
Maybe I should explain my question better. Suppose I enter my start
time
8:45 and end time 17:00. Excel will return a total time worked for the
day
as
8:15. If I work another day from 8:00 to 17:45 then it will give me
8:45.
Once I get the time worked for the day, using the chart I already gave,
I
want excel to convert it to decimal format. So in my first example it
should
convert it to 8.3 hours and the second example it should convert it to
8.8
hours. It's true that if I work 8 hours and 55 minutes then it should
give
me 9.0 hours.

"Rick Rothstein" wrote:

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)

Converting time to decimal format
 

Never mind I got it. Thanks a lot guys for all your help. Now I can apply
this to all my hourly employees so I don't have to spend hours converting it
myself.

"dwhapp" wrote:

Im assuming the B1-A1 reference concerns the end time minus the start time or
am I wrong. Finally, what happens if I add in two additional boxes for lunch
start time and lunch end time whereas:

A1 B1 C1 D1
start lunch start lunch end end
8:00 12:00 12:30 17:15

Currently I have the formula set at =(B1-A1)+(D1-C1) to give me the total
time worked for the day.


"Rick Rothstein" wrote:

Assuming your Start Time (8:45) is in A1 and your End Time (17:00) is in B1,
then you would use either of these two formulas to calculate the difference
and apply your chart at the same time...

Rick's Formula
========================
=HOUR(B1-A1)+ROUNDUP(MINUTE(B1-A1)/60,1)

Biff's Formula
========================
=HOUR(B1-A1)+CEILING(MINUTE(B1-A1)/60,0.1)

One note though... the values in the calculated column are what you see, so
when you add them up, you are adding up the rounded values, not the actually
calculated differences. So, for your example, the 8 hours 15 minutes
difference, which actually calculates to 8.25, will be added as 8.3 (not
8.25) when summed up.

--
Rick (MVP - Excel)


"dwhapp" wrote in message
...
Maybe I should explain my question better. Suppose I enter my start time
8:45 and end time 17:00. Excel will return a total time worked for the day
as
8:15. If I work another day from 8:00 to 17:45 then it will give me 8:45.
Once I get the time worked for the day, using the chart I already gave, I
want excel to convert it to decimal format. So in my first example it
should
convert it to 8.3 hours and the second example it should convert it to 8.8
hours. It's true that if I work 8 hours and 55 minutes then it should
give
me 9.0 hours.

"Rick Rothstein" wrote:

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)