Adding and subtracting hours in Excel 2000 worksheet

[Guest]

I'm having some success with the addition part but not with the subtracting part!

I have an Excel spreadsheet that is used to keep track of time worked by staff on a weekly basis and I need to keep track of the overtime worked. For example, one staff member has worked 40 hours, 54 minutes and 25 seconds in a week (displayed in a cell as 40:54:25 with the cell formatted as Category: --> Time and Type:--> 37:30:55).

I then went to subtract 40 hours from the total hours worked to get the overtime displayed and thats where I'm having the problem. I tried the format of "=F9 - 40:00:00" where cell F9 contains the total hours worked but I'm getting a strange figure back.

Any ideas anybody?

 

[Bob Phillips]

Glenn,

As 37 hours means no overtime, try something like
=IF(F9>40,F9-G9,0)
where F9 is hours worked, G9 is overtime threshold.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I'm having some success with the addition part but not with the subtracting part!

I have an Excel spreadsheet that is used to keep track of time worked by

staff on a weekly basis and I need to keep track of the overtime worked.
For example, one staff member has worked 40 hours, 54 minutes and 25 seconds
in a week (displayed in a cell as 40:54:25 with the cell formatted as
Category: --> Time and Type:--> 37:30:55).

I then went to subtract 40 hours from the total hours worked to get the

overtime displayed and thats where I'm having the problem. I tried the
format of "=F9 - 40:00:00" where cell F9 contains the total hours worked
but I'm getting a strange figure back.

 

[Guest]

Bob

Thanks for your response. However, you may have misunderstood the "37" hours part. This is simply the option within Excel for formatting the cells for displaying the hours-minutes-seconds

To clarify further, want to achieve the following
Cell Ref# Content Note
F2 27/10/2003 This is the week commencing on the Monday (dd/mm/yyyy
F3 32:54:25 Hours worked for the week
F5 8:00:00 One annual leave day taken in the wee
F9 40:54:25 Hours worked and annual leave hours added for the wee
F11 0:54:25 Time attributed as overtime to the employee (Cell F9 - 40 hours

Any ideas as to how to get this to happen

----- Bob Phillips wrote: ----

Glenn

As 37 hours means no overtime, try something lik
=IF(F9>40,F9-G9,0
where F9 is hours worked, G9 is overtime threshold

--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

I'm having some success with the addition part but not with th subtracting part

staff on a weekly basis and I need to keep track of the overtime worked
For example, one staff member has worked 40 hours, 54 minutes and 25 second
in a week (displayed in a cell as 40:54:25 with the cell formatted a
Category: --> Time and Type:--> 37:30:55)overtime displayed and thats where I'm having the problem. I tried th
format of "=F9 - 40:00:00" where cell F9 contains the total hours worke
but I'm getting a strange figure back

 

[David Turner]

=?Utf-8?B?R2xlbm4gSHVnZw==?= wrote

F11 0:54:25 Time attributed as overtime to the employee
(Cell F9 - 40 hours)

=IF(F9>TIME(40,0,0),F9-TIME(40,0,0),0) formatted as h:mm:ss

 

[Peo Sjoblom]

Won't work, the time function can't hold more than 24 hours and stay correct
so your formula
will return 24:54:25
to get overtime use

=MAX(F9-"40:00",0)

 

[David Turner]

Peo Sjoblom wrote

Won't work, the time function can't hold more than 24 hours and stay
correct so your formula
will return 24:54:25

Well, it returned the requested 0:54:25 in my test and worked for values up
to 24 hours overtime, but I can't argue if overtime >= 24 hours. As a
matter of fact, I can't argue period, because I just adapted a formula I
found in a Google search for "Overtime", and lucked out. I did get your
posted result if formatted as [h]:mm:ss rather than h:mm:ss.

=MAX(F9-"40:00",0)

mucho bettero

 

[Peo Sjoblom]

If I put 40:54:25 in F9 and use your formula I get 24:54:25

If you put =TIME(40,0,0) in a cell, then format the cell as general, if
indeed it is 40 hours you should get
1.66666666666667, I get 0.666666666666667 which is the same as 16:00.

--

Regards,

Peo Sjoblom

Peo Sjoblom wrote

Won't work, the time function can't hold more than 24 hours and stay
correct so your formula
will return 24:54:25

Well, it returned the requested 0:54:25 in my test and worked for values up
to 24 hours overtime, but I can't argue if overtime >= 24 hours. As a
matter of fact, I can't argue period, because I just adapted a formula I
found in a Google search for "Overtime", and lucked out. I did get your
posted result if formatted as [h]:mm:ss rather than h:mm:ss.

=MAX(F9-"40:00",0)

mucho bettero

 

[David Turner]

Peo Sjoblom wrote

If I put 40:54:25 in F9 and use your formula I get 24:54:25

I do, too, IF F11 is formatted as [h]:mm:ss

If you put =TIME(40,0,0) in a cell, then format the cell as general, if
indeed it is 40 hours you should get
1.66666666666667, I get 0.666666666666667 which is the same as 16:00.

You are absolutely correct.

Like I said, I lucked out when using the OP's cell entries and h:mm:ss.

Thanks for the lesson.

Apologies to the OP for steering him wrong.