Converting time to decimal format

[dwhapp]

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

 

[Glenn]

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)

 

[Rick Rothstein]

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.

 

[T. Valko]

=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

 

[T. Valko]

Typo:

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

Should be:

1:06 PM = 13.1

 

[T. Valko]

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

 

[Rick Rothstein]

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

 

[T. Valko]

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!

 

[Rick Rothstein]

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)

 

[Teethless mama]

Try this:

=ROUNDUP(A1*24,1)

 

[Glenn]

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

 

[dwhapp]

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.

 

[David Biddulph]

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.

 

[Rick Rothstein]

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.

 

[T. Valko]

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

 

[dwhapp]

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]

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)

 

[dwhapp]

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.