software

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: www.excelforum.com/member.php...oamp;userid=31845
View this thread: www.excelforum.com/showthread...hreadid=515655I 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.

HansReading 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)

quot;JoeDquot; gt; wrote in message
...
gt;
gt; I need to show a maximum time value and nothing more, even though the
gt; total calculation shows a greater value.
gt;
gt; This formula/calculation is bugging me, I've sorted out how to
gt; calculate time values, but stuck on the maximum amount allowed.
gt;
gt; If someone can help that would be great
gt;
gt; Joe
gt;
gt;
gt; --
gt; JoeD
gt; ------------------------------------------------------------------------
gt; JoeD's Profile:
www.excelforum.com/member.php...oamp;userid=31845
gt; View this thread: www.excelforum.com/showthread...hreadid=515655
gt;


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:
gt; Reading between the lines
gt;
gt; =MAX(TIME(8,0,0),your-calc_formula)
gt;
gt; will ensure a maximum of 8 hours
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;JoeDquot; gt; wrote in
gt; message
gt; ...
gt; gt;
gt; gt; I need to show a maximum time value and nothing more, even though
gt; the
gt; gt; total calculation shows a greater value.
gt; gt;
gt; gt; This formula/calculation is bugging me, I've sorted out how to
gt; gt; calculate time values, but stuck on the maximum amount allowed.
gt; gt;
gt; gt; If someone can help that would be great
gt; gt;
gt; gt; Joe
gt; gt;
gt; gt;
gt; gt; --
gt; gt; JoeD
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; JoeD's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31845
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=515655
gt; gt;--
JoeD
------------------------------------------------------------------------
JoeD's Profile: www.excelforum.com/member.php...oamp;userid=31845
View this thread: www.excelforum.com/showthread...hreadid=515655So 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)

quot;JoeDquot; gt; wrote in message
...
gt;
gt;
gt; Okay, maybe I wasn't so clear in what needed Excel to do.
gt;
gt; Bob you were nearly right. However, your formula did not give me the
gt; right answer.
gt;
gt; To be more exact; this formula will work in a flexi time sheet, but I
gt; need it to show that only 15hrs can be carried forward to the next
gt; flexi period.
gt;
gt; i.e.
gt; 40hrs x 4weeks = 160hrs
gt; 160hrs - 144basic hrs = 16hrs The sheet should show a max of 15hrs
gt; carried forward and not 16hrs as the calculation shows.
gt;
gt; I hope this makes some more sence Bob and I hope you can help further.
gt;
gt; Regards
gt;
gt; Joe
gt;
gt;
gt; Bob Phillips Wrote:
gt; gt; Reading between the lines
gt; gt;
gt; gt; =MAX(TIME(8,0,0),your-calc_formula)
gt; gt;
gt; gt; will ensure a maximum of 8 hours
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;JoeDquot; gt; wrote in
gt; gt; message
gt; gt; ...
gt; gt; gt;
gt; gt; gt; I need to show a maximum time value and nothing more, even though
gt; gt; the
gt; gt; gt; total calculation shows a greater value.
gt; gt; gt;
gt; gt; gt; This formula/calculation is bugging me, I've sorted out how to
gt; gt; gt; calculate time values, but stuck on the maximum amount allowed.
gt; gt; gt;
gt; gt; gt; If someone can help that would be great
gt; gt; gt;
gt; gt; gt; Joe
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; JoeD
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; JoeD's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=31845
gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=515655
gt; gt; gt;
gt;
gt;
gt; --
gt; JoeD
gt; ------------------------------------------------------------------------
gt; JoeD's Profile:
www.excelforum.com/member.php...oamp;userid=31845
gt; View this thread: www.excelforum.com/showthread...hreadid=515655
gt;


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(C6lt;=C9,C6,quot;15:00quot;)
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:
gt; So the number is decimal not time
gt;
gt; Maybe something like
gt;
gt; =MAX(MIN(time_worked-140,0),15)
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;JoeDquot; gt; wrote in
gt; message
gt; ...
gt; gt;
gt; gt;
gt; gt; Okay, maybe I wasn't so clear in what needed Excel to do.
gt; gt;
gt; gt; Bob you were nearly right. However, your formula did not give me
gt; the
gt; gt; right answer.
gt; gt;
gt; gt; To be more exact; this formula will work in a flexi time sheet, but
gt; I
gt; gt; need it to show that only 15hrs can be carried forward to the next
gt; gt; flexi period.
gt; gt;
gt; gt; i.e.
gt; gt; 40hrs x 4weeks = 160hrs
gt; gt; 160hrs - 144basic hrs = 16hrs The sheet should show a max of 15hrs
gt; gt; carried forward and not 16hrs as the calculation shows.
gt; gt;
gt; gt; I hope this makes some more sence Bob and I hope you can help
gt; further.
gt; gt;
gt; gt; Regards
gt; gt;
gt; gt; Joe
gt; gt;
gt; gt;
gt; gt; Bob Phillips Wrote:
gt; gt; gt; Reading between the lines
gt; gt; gt;
gt; gt; gt; =MAX(TIME(8,0,0),your-calc_formula)
gt; gt; gt;
gt; gt; gt; will ensure a maximum of 8 hours
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;JoeDquot; gt; wrote
gt; in
gt; gt; gt; message
gt; gt; gt; ...
gt; gt; gt; gt;
gt; gt; gt; gt; I need to show a maximum time value and nothing more, even
gt; though
gt; gt; gt; the
gt; gt; gt; gt; total calculation shows a greater value.
gt; gt; gt; gt;
gt; gt; gt; gt; This formula/calculation is bugging me, I've sorted out how to
gt; gt; gt; gt; calculate time values, but stuck on the maximum amount allowed.
gt; gt; gt; gt;
gt; gt; gt; gt; If someone can help that would be great
gt; gt; gt; gt;
gt; gt; gt; gt; Joe
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; JoeD
gt; gt; gt; gt;
gt; gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; gt; JoeD's Profile:
gt; gt; gt; www.excelforum.com/member.php...oamp;userid=31845
gt; gt; gt; gt; View this thread:
gt; gt; gt; www.excelforum.com/showthread...hreadid=515655
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; JoeD
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; JoeD's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31845
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=515655
gt; gt;--
JoeD
------------------------------------------------------------------------
JoeD's Profile: www.excelforum.com/member.php...oamp;userid=31845
View this thread: www.excelforum.com/showthread...hreadid=515655Not much point in returning quot;15:00quot; if you want to use this in other
formulae, as this will give you a text value.

Pete