+ Reply to Thread
Results 1 to 4 of 4

Need help fixing a formula [ to calculate pay depending on what time you work]

  1. #1
    Registered User
    Join Date
    12-14-2017
    Location
    Minnesota
    MS-Off Ver
    2010
    Posts
    7

    Need help fixing a formula [ to calculate pay depending on what time you work]

    Hello,

    I'm having issues with a formula to calculate pay depending on what time you work.

    I've attached my sheet. For many of the punches it will correctly calculate the standard hours and the peak hours. Standard hours being between 6AM - 4PM with peak being everything else.

    My standard formula with start cell F4 and End cell G4 is:
    =24*IF(F4="","0",(IF(MOD(F4,1)<MOD(G4,1),MIN(MOD(G4,1),K33)-MAX(MOD(F4,1),K34),MAX(0,K33-MOD(F4,1))+MAX(0,MOD(G4,1)-K34))))

    And my peak hours formula is:
    =24*(G4-F4)-H4

    K33 being peak hours at 4:00 PM and K34 being standard at 6:00 AM.

    With my sheet if I start at 10AM and end at 10PM it correctly calculates 6 standard hours and 6 peak hours. However if I start at 5PM and end at 10PM it calculates -1 standard hour and 6 peak hours. It gives me the correct amount of total hours, but messes up the pay.

    I would also like to make the formula so it can calculate across two days. Example - 3PM to 1AM the next day. This should show 1 hour standard and 9 hours peak.

    Any advice?
    Attached Files Attached Files

  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: Need help fixing a formula [ to calculate pay depending on what time you work]

    Please add sample data and expected results to your file.

  3. #3
    Registered User
    Join Date
    12-14-2017
    Location
    Minnesota
    MS-Off Ver
    2010
    Posts
    7

    Re: Need help fixing a formula [ to calculate pay depending on what time you work]

    Hello,

    The file is attatched.

    I am looking to get correct standard hours on cell H4 and peak hours on I4. Peak hours starting at 4PM on cell K33 and standard hours on K34. I would also like assistance on having it calculate correct hours if the shift spans two days. Example - Working from 10PM until 6AM the next day.

    Currently some shifts work correctly, but others show negative hours for standard and then incorrect hours on peak. Total hours still show correct though.

    Any assistance would be greatly appreciated.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Need help fixing a formula [ to calculate pay depending on what time you work]

    Try modifying the formulas as follows:
    1) In cell H4 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2) In cell I4 and down: =24*(G4+(G4<F4)-F4)-H4
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 1
    Last Post: 06-10-2016, 11:38 PM
  2. [SOLVED] CF for time difference - fixing formula
    By Lukael in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 07-27-2014, 05:00 PM
  3. Replies: 9
    Last Post: 07-13-2014, 11:09 PM
  4. calculate work time to money
    By yaelb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-09-2013, 08:23 AM
  5. [SOLVED] Formula to calculate sum depending on a value
    By rox-cc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-04-2013, 10:59 AM
  6. time:how to work out how to calculate my finish time
    By Crasher in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-15-2006, 04:19 AM

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