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


--
JoeD
------------------------------------------------------------------------
JoeD's Profile: http://www.excelforum.com/member.php...o&userid=31845
View this thread: http://www.excelforum.com/showthread...hreadid=515655

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

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" wrote in message
...

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


--
JoeD
------------------------------------------------------------------------
JoeD's Profile:
http://www.excelforum.com/member.php...o&userid=31845
View this thread: http://www.excelforum.com/showthread...hreadid=515655

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 the
right answer.

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

i.e.
40hrs x 4weeks = 160hrs
160hrs - 144basic hrs = 16hrs The sheet should show a max of 15hrs
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 Wrote:
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" wrote in
message
...

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


--
JoeD

------------------------------------------------------------------------
JoeD's Profile:
http://www.excelforum.com/member.php...o&userid=31845
View this thread:
http://www.excelforum.com/showthread...hreadid=515655



--
JoeD
------------------------------------------------------------------------
JoeD's Profile: http://www.excelforum.com/member.php...o&userid=31845
View this thread: http://www.excelforum.com/showthread...hreadid=515655

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" wrote in message
...


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 the
right answer.

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

i.e.
40hrs x 4weeks = 160hrs
160hrs - 144basic hrs = 16hrs The sheet should show a max of 15hrs
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 Wrote:
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" wrote in
message
...

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


--
JoeD

------------------------------------------------------------------------
JoeD's Profile:
http://www.excelforum.com/member.php...o&userid=31845
View this thread:
http://www.excelforum.com/showthread...hreadid=515655



--
JoeD
------------------------------------------------------------------------
JoeD's Profile:
http://www.excelforum.com/member.php...o&userid=31845
View this thread: http://www.excelforum.com/showthread...hreadid=515655

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



Bob Phillips Wrote:
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" wrote in
message
...


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
the
right answer.

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

i.e.
40hrs x 4weeks = 160hrs
160hrs - 144basic hrs = 16hrs The sheet should show a max of 15hrs
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 Wrote:
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" wrote
in
message
...

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


--
JoeD


------------------------------------------------------------------------
JoeD's Profile:
http://www.excelforum.com/member.php...o&userid=31845
View this thread:
http://www.excelforum.com/showthread...hreadid=515655



--
JoeD

------------------------------------------------------------------------
JoeD's Profile:
http://www.excelforum.com/member.php...o&userid=31845
View this thread:
http://www.excelforum.com/showthread...hreadid=515655



--
JoeD
------------------------------------------------------------------------
JoeD's Profile: http://www.excelforum.com/member.php...o&userid=31845
View this thread: http://www.excelforum.com/showthread...hreadid=515655

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