How can I show a maximum number?

[JoeD]

I need to show a maximum time value and nothing more, even though the
total calculation shows a greater value.

This formula/calculation is bugging me, I've sorted out how to
calculate time values, but stuck on the maximum amount allowed.

If someone can help that would be great

Joe

 

[flummi]

I fear this is not what you were looking for . But I'll nevertheless
try:

11:12
10:15
16:40
07:10
23:50

23:50

Formula in A7: =MAX(A1:A5)

If this is not your request you may want to be a bit more specific.

Hans

 

[Bob Phillips]

Reading between the lines

=MAX(TIME(8,0,0),your-calc_formula)

will ensure a maximum of 8 hours

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[JoeD]

Okay, maybe I wasn't so clear in what needed Excel to do.

Bob you were nearly right. However, your formula did not give me th
right answer.

To be more exact; this formula will work in a flexi time sheet, but
need it to show that only 15hrs can be carried forward to the nex
flexi period.

i.e.
40hrs x 4weeks = 160hrs
160hrs - 144basic hrs = 16hrs The sheet should show a max of 15hr
carried forward and not 16hrs as the calculation shows.

I hope this makes some more sence Bob and I hope you can help further.

Regards

Joe

 

[Bob Phillips]

So the number is decimal not time

Maybe something like

=MAX(MIN(time_worked-140,0),15)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[JoeD]

I have sorted out the Formula/Function that I need.

Your one may work also Bob, but I haven't tried yet.

Just in case you would need it, you can try;

=IF(C6<=C9,C6,"15:00")
or a much smaller formula like
=MIN(C9,C6)

C9 being a hidden cell which is used for reference
C6 being the total time calculator

Both seem to work and do the job, but I need to see which one works
best when it is used with the other formulas in the sheet.

Yours may work also Bob, and I will try it in a second.

Thanks

Joe

 

[Pete_UK]

Not much point in returning "15:00" if you want to use this in other
formulae, as this will give you a text value.

Pete