Converting overtime to decimal

[MarkC]

Hello all.

I'm working on developing a time sheet for were I work and I need a
little help.

I have everything working right except the overtime conversion.

Here's what I have and what I need:

I have time blanks for what hours they work (entered as excel time or
military time, converted to excel time: 17:00=5:00 PM

I have a field that converts the hours entered into hours worked. The
field then looks and sees if it is over 8 hours if it is, then put down
8:00 in a blank and put the remaining time in the overtime field, IE: I
worked 8:45 mins in one day (8 hours 45 minutes). 8:00 goes into hours
worked, and 0:45 goes into the ovetime field.

The problem stems from our payroll software won't let us enter the
overtime worked as time, rather as decimal, so I need to convert the
0:45 into 0.75, or 3/4 of an hour.

However, excel doesn't see it like I do, and I'm officially stumped.
Setting the cell to percentage produces 9.38% for the 0:45, and I've
tried every math function i can think of. Any help on this would be
appreciated

 

[Harald Staff]

Hi

See
http://www.cpearson.com/excel/datetime.htm
on this. Short version: 1 is a day. So 1 hour is not 1 but 1/24. To make 8:00 (=8/24 = 1/3
= 33.333%) become 8.0 you must multiply 8:00 by 24.

 

[mnewdick]

Have the time in hours and minutes in cell A1 (formatted as a number).
Your entry might be 9.45 (meaning 9 hours, 45 minutes). Then writ
this in cell B1:

=ROUND(A1,0)+(A1-ROUND(A1,0))/60*100

That will solve it assuming you won't exceed 50 minutes in a partia
hour (and given what you're doing, that's unlikely).

In the formula, cell A1 might have to be the combination of two cell
(if that's how your data is laid out) -- play with it.

Hope this helps.

Mar

 

[Lady Layla]

Well this message got way out of line here --- how did it show up as a reply to
Re: Decimal?


: Have the time in hours and minutes in cell A1 (formatted as a number).
: Your entry might be 9.45 (meaning 9 hours, 45 minutes). Then write
: this in cell B1:
:
: =ROUND(A1,0)+(A1-ROUND(A1,0))/60*100
:
: That will solve it assuming you won't exceed 50 minutes in a partial
: hour (and given what you're doing, that's unlikely).
:
: In the formula, cell A1 might have to be the combination of two cells
: (if that's how your data is laid out) -- play with it.
:
: Hope this helps.
:
: Mark
:
:
: ---
: Message posted
: