+ Reply to Thread
Results 1 to 8 of 8

Calculating time help?

  1. #1
    Registered User
    Join Date
    12-06-2019
    Location
    Florida
    MS-Off Ver
    2016
    Posts
    10

    Calculating time help?

    I have an excel sheet that I use to track my time and I am trying to figure out how to cap on certain cells with a max amount and add it to another cell, I am working in time increments so I am not to fluent with how it works.
    what I need is to have my total time stop at 40 hours and the remaining time to be placed into the OT cell for overtime. Ex: I worked 43:30 hours this week so I need totals cell to say 40 hours and the OT cell to say 3:30

    any help that would be appreciated!
    Attached Files Attached Files
    Last edited by KarolSweats; 12-06-2019 at 10:24 AM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Calculating time help?

    Hello and welcome to the forum.

    I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  3. #3
    Registered User
    Join Date
    12-06-2019
    Location
    Florida
    MS-Off Ver
    2016
    Posts
    10

    Re: Calculating time help?

    hopefully the excel sheet will help, i uploaded it. what it is, is cell G14 has the total and would like the remainder to go to C15

  4. #4
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,375

    Re: Calculating time help?

    you can use min and MAX to find show 40 or less for PTO and 0 or more for OT

    PTO
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    OT
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    see also attachment
    Attached Files Attached Files

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Calculating time help?

    You can use this in G14:
    =MIN(5/3,SUM(G3:G13))
    Formatted as [h]:mm

    and this in C15:
    =MAX(SUM(G3:G13)-5/3,0)
    Formatted as [h]:mm

  6. #6
    Registered User
    Join Date
    12-06-2019
    Location
    Florida
    MS-Off Ver
    2016
    Posts
    10

    Re: Calculating time help?

    Awesome, I didn't think to try that

    thanks for the help!

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Calculating time help?

    You're welcome. Happy to help.

    Thanks for the rep!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  8. #8
    Registered User
    Join Date
    08-12-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    39

    Re: Calculating time help?

    Error post, sorry.

+ 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] Calculating a previous work shift time (05:30, or 17:30) based on current time
    By andrew.why in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-20-2017, 09:09 PM
  2. Replies: 3
    Last Post: 08-12-2017, 01:48 AM
  3. Calculating staff roster time to hours, depending on time worked.
    By cookiet in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-16-2013, 10:25 AM
  4. Replies: 3
    Last Post: 11-21-2012, 05:47 AM
  5. Formula for Calculating Paid Time Off for fiscal anniversary date real time
    By 168rockwood in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-07-2012, 04:15 PM
  6. 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
  7. Excel Formulas for Calculating Straight, Over Time & Double Time in Cost Estimating
    By redhairredhair in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-23-2010, 09:06 PM

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