# Running 2 If/Then Conditions for Time Clock

1. ## Running 2 If/Then Conditions for Time Clock

Hello Excel Geniuses!

I am in need of some help to get the brain juices flowing on this Monday morning. Here's the scoop:

I have created a time card calculator to calculate total time worked and distribute it to regular and overtime hours. Currently I have it set to calculate if over 8 hours then add the remaining as overtime, seems simple enough. This works for a day by day basis but I also need it to run against a running total of 40 hours per week. Meaning if more than 40 hours worked, then add all remaining entries as overtime.

I have tried creating an additional column to calculate the running total and then doing a simple if/then to return a 0 if under 40 or a 1 if over 40. But I am stuck in trying to do a nested If/then to check for the 40 hours before checking for the 8 hours. I've attached the spreadsheet so you can look under the hood. Can anyone PLEASE help point me in the right direction???

Many thanks!  Register To Reply

2. ## Re: Calling All Excel Geniuses: A fun little If/Then Problem for Time cards

In L12

=MAX(0,J12-40)

???  Register To Reply

3. ## Re: Calling All Excel Geniuses: A fun little If/Then Problem for Time cards

John thank you so much for the quick reply. That formula would work for the totals (row 12) but I also need it to display in the Regular and Overtime columns as well (J & M). In this example it would only calculate a max of 40 hours for column J and then anything over that would automatically be calculated in column M. Ideally row 12 would just show the sums for rows 4-10, since the if/then would have already moved anything over 40 or 8 hours into overtime. Please let me know if I'm not explaining this properly?  Register To Reply

4. ## Re: Calling All Excel Geniuses: A fun little If/Then Problem for Time cards

You seem to have 2 conditions:

on ANY day > 8 hours is overtime. irrespective if total hours worked in a week > 40.

If total hours > 40 , this is also overtime so in your example, overtime is 3.5 hours (2 hours of "Daily" and 1.5 of "Weekly") ????

My first formula could be placed in K12.  Register To Reply

5. ## Re: Calling All Excel Geniuses: A fun little If/Then Problem for Time cards

title updated  Register To Reply

6. ## Re: Running 2 If/Then Conditions for Time Clock

I have tried a different approach and nearly have it there but am still needing it to automatically move any Overtime after 40 hours into the Overtime hours column in the L & M columns. The formula currently calculates the right totals, but I'd also like it to display them in the appropriate columns. For example, I used the following numbers and on Saturday the 3.75 hours should not all be in the Reg hours but rather 2.25 in Reg and 1.5 in Overtime. Can anyone help point me in the right direction?  Register To Reply

7. ## Re: Running 2 If/Then Conditions for Time Clock

Try

in L7

=MIN(IF(((J7-G7)-(I7-H7))*24>8,8,((J7-G7)-(I7-H7))*24),40-SUM(\$L\$6:L6))

Copy down

in M7

=N7-L7

copy down  Register To Reply

8. ## Re: Running 2 If/Then Conditions for Time Clock

Thank you thank you thank you Jon!!!! That did the trick, I can't tell you how much I appreciate your time and efforts!  Register To Reply

9. ## Re: Running 2 If/Then Conditions for Time Clock

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.   Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 