# Capping a [hh]:mm

1. ## 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..

2. ## Re: Capping a [hh]:mm

=MIN(56,hours)

3. ## 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.

4. ## Re: Capping a [hh]:mm

Originally Posted by JohnTopley
=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).

5. ## Re: Capping a [hh]:mm

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

7. ## 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. ## 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)

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

B1= calculated total hours

cell formatted as [h]:mm

9. ## Re: Capping a [hh]:mm

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

10. ## Re: Capping a [hh]:mm

Originally Posted by JohnTopley
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. ## 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. ## Re: Capping a [hh]:mm

Show us what formula you're using to get the 79hrs.

13. ## 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. ## Re: Capping a [hh]:mm

If you have your hours in cells A1:F1, put one of these where you want the sum:
Formula:
`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:
`Please Login or Register  to view this content.`

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