Multiple IF values in one cell?

[FletchR]

Very simple time sheet question for a formula that says “if the value inthis cell >8, then put the answer to value-8. If the value is <8, just put the value 0 in this cell “

So, my time sheet looks like this:

[TABLE="class: grid, width: 15"]
<tbody>[TR]
[TD]In[/TD]
[TD]out[/TD]
[TD]Hours[/TD]
[TD]OT[/TD]
[TD]OTusedForMakeUpTime[/TD]
[TD]CumulativeOT[/TD]
[/TR]
[TR]
[TD]7.00[/TD]
[TD]20.75[/TD]
[TD]13.75[/TD]
[TD]5.75[/TD]
[TD][/TD]
[TD]5.75[/TD]
[/TR]
</tbody>[/TABLE]
6.75. 16.00 9.25. 1.25. 7.00
6.50. 12.50. 6.00. 2.00. 5.00

I want the OT field to auto calculate if I worked more than 8 hrs, how many more? And if I worked less than 8 hrs then just put 0. If you really want to get fancy, we could say if OT = 0, then 8-hours= how much time I need to make up and then subtract that amount from Cumulative OT. Please and thank you.

 

[philwojo]

Try this

=IF(C2>8,C2-8,0)

Assumes you values are in A, B, C and D, with OT being in D.

Put that formula in D2 and copy down.

From you example I wouldn't expect the "2.00" in your 3rd entry, I would expect zero since you only worked 6 hours.

 

[FletchR]

Thanks! Your formula makes sense. But if I only worked 6 hrs and owe ‘2 now, how do I get the cum ot hours down by 2?

 

[philwojo]

You didn't say that in your OP, you said if it is 8 or less to put zero.

So what do you want it to show if it is less than 8 then?

To me the OT column should remain 0 as if you work less than 8 hours you don't get OT, but if you want it to be something else that can be done.

 

[philwojo]

Thanks! Your formula makes sense. But if I only worked 6 hrs and owe ‘2 now, how do I get the cum ot hours down by 2?

Sorry, just reread what you typed, let me think on that, or maybe someone else can as I'm about done for the night.

 

[philwojo]

Put this formula in F2:

=(C2-8)

Then put this in F3 and copy down:

=F2+(C3-8)

 

[FletchR]

Let me try something different. I think you can't see all the column headers across the top. I have time in, time out, hours, OT hours, Hours I owe (less than 8 hrs worked) and then cumulative OT.

So, I enter time military style. I usually work at least 8 hrs a day, but if I need to leave early for an appointment, I enter the hours I still owe (8-time missed).

Finally, in the Cumulative OT column, if I have worked OT, I want the # of hours over 8 to be added to this column, and if I have left early and owe hours, I want the hours I owe to be subtracted from the cumulative OT column. that way I have a running tally of whether I'm ahead of the game, or behind.

Does that make better sense?

btw - I used to live in Naperville. Went to NCC.

Put this formula in F2:

=(C2-8)

Then put this in F3 and copy down:

=F2+(C3-8)

 

[philwojo]

Ok, so you have changed what you want from your original post, the formulas I gave you in post #6 should do what you are asking for the Cumulative OT column, you have to start with something in F2, to that is the 1st one, then from there you look back to your previous cell and add in the number of hours of OT that you worked, or that you owe.

Are you not getting the results you expect as based on your data it is working for me.