+ Reply to Thread
Results 1 to 3 of 3

Calculating time with three possible pay rates based on time of day

  1. #1
    Registered User
    Join Date
    07-03-2014
    Location
    Toronto, ON
    MS-Off Ver
    2013
    Posts
    1

    Calculating time with three possible pay rates based on time of day

    So here's my problem, I've got a timesheet estimate form that I'm putting together and I can't get the time calculation to work correctly.

    The setup is simple:

    I2 = Start Time
    J2 = End Time
    K2 = Total Shift Hours estimated

    Now this is where it gets tricky for me, I need to calculate the hours worked that fall into three possible categories:

    Normal time = between 07:00 and 15:00
    Afternoon premium = between 15:00 and 23:00
    Overnight premium = between 23:00 and 07:00

    In an effort to make it easier for the people doing the data entry I'm trying to avoid them entering more than a single start and single end time for each line of data.

    I've reviewed several examples and have written so many variations of different formulas that I'm going a little cross-eyed at this point and am in serious need of help.

    Attached is the sample I'm working with at the moment....

    Timesheet_Test.xlsx

  2. #2
    Forum Contributor
    Join Date
    06-28-2004
    MS-Off Ver
    2010
    Posts
    225

    Re: Calculating time with three possible pay rates based on time of day

    I'm sure this probably needs some tweaking.....but I took a shot.
    I added a time column (15 min intervals) and the SHIFT next to it.
    Added columns N,O, & P for 1st, 2nd, and 3rd shifts.
    If they work prior or over the shift times it separates the times by shift.
    There may still be some bugs....but my tests were working ok.
    Good luck - Hope this helps!!
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-28-2015
    Location
    Kansas City
    MS-Off Ver
    Windows 7
    Posts
    1

    Re: Calculating time with three possible pay rates based on time of day

    Awesome work. Now, what if we wanted to calculate based on time spent greater than a 24 hour period still broken down per shift and the rates for those shifts? Imagine I send a construction crew to a site that takes 5 days to complete. I bill the client X rate during shift 1, Y rate during shift 2, and Z rate during shift 3, but you would have maybe "19" hours calculated during shift 1, 38 in shift 2 etc.

+ 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. Calculating the start time based on known end time
    By Journeyman3000 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-20-2014, 10:41 PM
  2. Replies: 0
    Last Post: 01-10-2013, 03:07 PM
  3. Replies: 4
    Last Post: 11-27-2012, 04:42 PM
  4. Calculating END time based on start time, breaks, and hours to complete
    By sweedey in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-01-2012, 11:29 AM
  5. Calculating Time Owed based on Time Worked
    By Tony Vargo in forum Excel General
    Replies: 9
    Last Post: 06-23-2009, 11:30 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