+ Reply to Thread
Results 1 to 9 of 9

Running 2 If/Then Conditions for Time Clock

  1. #1
    Registered User
    Join Date
    02-13-2018
    Location
    CA
    MS-Off Ver
    2010
    Posts
    4

    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!
    Attached Files Attached Files
    Last edited by keross; 02-27-2018 at 01:31 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

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

    In L12

    =MAX(0,J12-40)

    ???

  3. #3
    Registered User
    Join Date
    02-13-2018
    Location
    CA
    MS-Off Ver
    2010
    Posts
    4

    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?

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    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.

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

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

    title updated
    Last edited by protonLeah; 02-26-2018 at 11:08 PM.
    Ben Van Johnson

  6. #6
    Registered User
    Join Date
    02-13-2018
    Location
    CA
    MS-Off Ver
    2010
    Posts
    4

    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?
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    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

  8. #8
    Registered User
    Join Date
    02-13-2018
    Location
    CA
    MS-Off Ver
    2010
    Posts
    4

    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!

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using a UserForm to Display Playing Cards - problem with overlapping cards
    By joebbb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-24-2017, 04:49 PM
  2. Hello Guys! Y'all Excel Geniuses ^^
    By joeybidan in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 08-25-2014, 10:23 PM
  3. For Excel geniuses
    By Garratt in forum Excel General
    Replies: 3
    Last Post: 04-11-2006, 02:40 PM
  4. [SOLVED] how to subtract time cards in minutes in excel
    By excelsior in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-06-2005, 01:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1