+ Reply to Thread
Results 1 to 14 of 14

Capping a [hh]:mm

  1. #1
    Registered User
    Join Date
    05-19-2016
    Location
    Devon
    MS-Off Ver
    Apple
    Posts
    4

    Capping a [hh]:mm

    Hi all. My problem is I am trying to set up a data base with time in it. All columns are formatted to [hh]:mm and I need the final hours to be capped...eg

    34:00hrs + 45:00hrs = 79hrs. But I need the final sum (79hrs) to be a maximum of 56:00hrs if above 56:00hrs, if below, the true sum to be shown..


    Can anyone help me please!

  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
    27,999

    Re: Capping a [hh]:mm

    =MIN(56,hours)

  3. #3
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Capping a [hh]:mm

    Hi -

    Try this:

    =IF(C20+D20>56/24,56/24,C20+D20) This formula assumes your two hour values are in C20 and D20.
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Capping a [hh]:mm

    Quote Originally Posted by JohnTopley View Post
    =MIN(56,hours)
    John,
    This will cap at 56 days, not 56 hours.

    It's a trivial fix but something like:
    Please Login or Register  to view this content.
    (Because TIME(56,0,0) will assess as 8 AM because it discounts the 48 hours of 4 even days).
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  5. #5
    Registered User
    Join Date
    05-19-2016
    Location
    Devon
    MS-Off Ver
    Apple
    Posts
    4

    Re: Capping a [hh]:mm

    Thanks for replying. I must be stupid as both don't work. Loginmor, your format returns as false.

  6. #6
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Capping a [hh]:mm

    It would probably help if you uploaded a sample of your spreadsheet. That way we can see your actual data.

  7. #7
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Capping a [hh]:mm

    Don't work how?

    What exactly did you put in each cell? Can you post an example worksheet for us to examine?

  8. #8
    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
    27,999

    Re: Capping a [hh]:mm

    I was showing the principle, not the actual format : the hours was reference to the calculated hours (not clear I admit)

    As per Ben's reply

    =MIN(TIME(1,0,0)*56,B1)

    B1= calculated total hours

    cell formatted as [h]:mm

  9. #9
    Registered User
    Join Date
    05-19-2016
    Location
    Devon
    MS-Off Ver
    Apple
    Posts
    4

    Re: Capping a [hh]:mm

    Thanks for all your help.....I was trying to sort it out with all your ideas.

  10. #10
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Capping a [hh]:mm

    Quote Originally Posted by JohnTopley View Post
    I was showing the principle, not the actual format : the hours was reference to the calculated hours (not clear I admit)
    Yeah I only found it because I had the same idea and couldn't figure out why it was returning 79:00 at first.

  11. #11
    Registered User
    Join Date
    05-19-2016
    Location
    Devon
    MS-Off Ver
    Apple
    Posts
    4

    Re: Capping a [hh]:mm

    Sorry to cause troube to you, but I have to go now...bed. but I will check any ideas tomorrow

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Capping a [hh]:mm

    Show us what formula you're using to get the 79hrs.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  13. #13
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Capping a [hh]:mm

    I would like to see a spreadsheet with the actual data because if it's not based on Excel 24 hour ratios, then we're all going the wrong direction.

  14. #14
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Capping a [hh]:mm

    If you have your hours in cells A1:F1, put one of these where you want the sum:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    All the cells should be formatted as [hh]:mm before you start.

    You could use this IF instead, but the MIN above is easier
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

+ 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. ETP tax capping formulas
    By Belinda Shelley in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-08-2014, 11:58 PM
  2. 'capping' a cell value at 100%
    By dpcp in forum Excel General
    Replies: 5
    Last Post: 05-01-2012, 09:15 AM
  3. Capping a cell value
    By Max9 in forum Excel General
    Replies: 2
    Last Post: 03-25-2011, 03:16 AM
  4. capping cell value
    By excelnonwizard in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-18-2010, 03:38 AM
  5. Capping results
    By djarcadian in forum Excel General
    Replies: 4
    Last Post: 01-05-2006, 05:20 PM
  6. [SOLVED] Capping a formula value?
    By Scot B in forum Excel General
    Replies: 2
    Last Post: 12-27-2005, 07:45 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