Closed Thread
Results 1 to 4 of 4

Start and End Times for Both Regular and Overtime Hours from General Hours Worked?

  1. #1
    Registered User
    Join Date
    04-22-2016
    Location
    Houston, TX
    MS-Off Ver
    2011
    Posts
    7

    Start and End Times for Both Regular and Overtime Hours from General Hours Worked?

    Hi All,

    Our client is a medical professional who essentially got screwed (and VERY overworked) by his employer. I need to know how to draw out overtime start and end times, as well as the regular hour start and end times, from the total times worked for each day he recorded his general hours. Basically pay schedule is tiered like so:

    (A) Weekday regular hours: 7am-3pm (8 hr total)
    (B) Weekdays on call [overtime hours]: 3pm-7am (16 hr total)
    (C) Some weekends on call [overtime hours]: 7am (Sat.) - 7am (Mon.)

    He is also entitled to payment on the weekends as set out in (C), but I can handle that manually if need be. I just really need help setting up a formula to parse out start and end times of (A) and (B) from the generalized hours he gave us in the spreadsheet.

    Very much obliged,

    Frustrated Law Clerk
    Last edited by Lawclerk; 04-23-2016 at 01:09 AM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,717

    Re: 16 Hour Night Differential Calculation

    how is the data setout
    perhaps a sample

    you can use a weekday() to identify the weekend and monday

    =WEEKDAY(A1,2)>5
    that will tell you its a weekend

    then you can add some times in and using an
    IF , OR , AND
    and then you could have a column with a 1 to show the overtime

    but how about
    Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "Choose File" (top Left corner).
    Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    04-22-2016
    Location
    Houston, TX
    MS-Off Ver
    2011
    Posts
    7

    Re: 16 Hour Night Differential Calculation

    Okay, so line 6 contains a sample of how I want the information to come out. I will limit the scope of my question to weekday regular hours and overtime.

    1. "E column" and "F column" (start and end regular shift hours) should be derived from "C and D column" (start shift and end shift), and should consist of the start and end time of hours worked other than the overtime hours of 3 pm - 7 am on weekdays.

    2. "H column" and "I column" (O/T start and O/T end) also should be derived from "C and D column" (start and end shift) and should consist of the start and end times of overtime hours of 3 pm - 7 am on weekdays.


    I'm pretty sure I can wing the rest. If you think I'm royally off track then by all means do comment. I just don't want anyone to have to dedicate too much time to this.

    Many, many thanks in advance.

    -Frustrated LawClerk
    Attached Files Attached Files
    Last edited by Lawclerk; 04-23-2016 at 12:29 AM.

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: Start and End Times for Both Regular and Overtime Hours from General Hours Worked?

    Thread is opened in Commercial Serives.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to count hours for Night Differential (using IF function)
    By helpme10 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-30-2019, 05:07 PM
  2. [SOLVED] Night Shift Differential
    By vaium in forum Excel General
    Replies: 9
    Last Post: 04-15-2016, 11:05 AM
  3. Computing for number of hours under Night Differential
    By emanon132501 in forum Excel General
    Replies: 4
    Last Post: 09-24-2015, 02:13 AM
  4. [SOLVED] Compute Night Differential Hours
    By UnKnown_25 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 08-04-2015, 11:17 AM
  5. TIME DIFFERENCE - Calculate Night Differential
    By persnickety in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-12-2013, 12:44 PM
  6. How to calculate Night Differential
    By mar_t in forum Excel General
    Replies: 10
    Last Post: 01-04-2013, 01:55 AM
  7. Replies: 2
    Last Post: 07-27-2012, 04:54 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