+ Reply to Thread
Results 1 to 10 of 10

Hours Paid Rules

  1. #1
    Banned User!
    Join Date
    09-26-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    607

    Hours Paid Rules

    Hello all,

    I have some scenarios for paid hours that are for occasional circumstances.

    The paid hours are accumulated on the 'Time Clock Log'.

    On the 'Switchboard' sheet I listed some rules and as it is now the paid hours are not correct for some of these circumstances. See pink cells G77:G79 on 'Time Clock Log'.

    Can someone please help me with the formulas for these rules?

    Thank you very much,
    Billy

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Hours Paid Rules

    your rules are not so clear
    1) would 5:05 stay as 5:05
    4)
    7) would 39:50 get rounded to 39:58 ?

    =IF(AND(D75-C75<=1/4,E75="",F75=""),D75-C75,IF(WEEKDAY($B75,2)>5,IF(COUNT(C75:D75)=2,((D75-C75)+(IF(COUNT(E75:F75)=2,F75-E75))),""),IF(COUNT(C75:D75)=2,IF(MIN(D75,D$2)-MAX(C75,C$2)<=0,0,MIN(D75,D$2)-MAX(C75,C$2)+(IF(COUNT(E75:F75)=2,IF(MIN(F75,F$2)-MAX(E75,E$2)<=0,0,MIN(F75,F$2)-MAX(E75,E$2))))),"")))

    would solve some of your problems. the formula is starting to get very long!

  3. #3
    Banned User!
    Join Date
    09-26-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    607

    Re: Hours Paid Rules

    Yes maybe the formula is too long. Is there another way to accomplish?

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Hours Paid Rules

    if you worked out a morning and afternoon as separate columns, it would make things a little shorter

  5. #5
    Banned User!
    Join Date
    09-26-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    607

    Re: Hours Paid Rules

    I have an idea to simplify things. I added helper columns to adjust times for shift schedules that apply to weekdays.

    What are your thoughts? Check the 'Time Sheet Log' for weekdays rows 75 to 79. The only thing so far I haven't accounted for is if they work without a lunch break.

  6. #6
    Registered User
    Join Date
    12-21-2015
    Location
    United States
    MS-Off Ver
    2016
    Posts
    62

    Re: Hours Paid Rules

    you could change your lunch criteria to not automatically add even when the punch doesnt exist in D
    Last edited by Skiptomylou; 03-16-2018 at 12:12 PM.

  7. #7
    Banned User!
    Join Date
    09-26-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    607

    Re: Hours Paid Rules

    I tried this formula

    =IF(I75<>"",(H75-G75)+(J75-I75),(H75-G75))

  8. #8
    Banned User!
    Join Date
    09-26-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    607

    Re: Hours Paid Rules

    How can I incorporate a mandatory lunch break of 30 minutes if they work at least 6 hours?

    That would mean I75-H75 is >=30 minutes but how to formulate and account for if blank cells?
    Last edited by Billy Spivy; 03-16-2018 at 12:14 PM.

  9. #9
    Registered User
    Join Date
    12-21-2015
    Location
    United States
    MS-Off Ver
    2016
    Posts
    62

    Re: Hours Paid Rules

    Because your time and data validation make it a little tricky, you could use IF(LEN(D75)<1

  10. #10
    Banned User!
    Join Date
    09-26-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    607

    Re: Hours Paid Rules

    Okay thank you I will try that later but for now I am getting a weird REF# in cell DY3 can you have a look please?
    Last edited by Billy Spivy; 03-16-2018 at 01:23 PM.

+ 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. [SOLVED] Paid Time Off Hours Formula
    By Robert1960 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-04-2016, 10:29 AM
  2. Count workers PAID hours; colors; design problems
    By tigfur in forum Excel General
    Replies: 4
    Last Post: 04-01-2016, 01:39 AM
  3. Replies: 5
    Last Post: 11-11-2015, 03:52 PM
  4. Replies: 4
    Last Post: 01-09-2015, 12:27 PM
  5. Replies: 7
    Last Post: 08-14-2013, 07:04 PM
  6. Total hours and paid hours calculation advice
    By lozzauk in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-27-2013, 08:25 AM
  7. [SOLVED] Outlook 2010 - Creating Rules - Rules Constant
    By Jack7774 in forum Outlook Formatting & Functions
    Replies: 3
    Last Post: 03-18-2013, 03:48 PM

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