Formula question

  • Thread starter Thread starter Roberta
  • Start date Start date
R

Roberta

I need help with getting a formula to work.

I have a spreadsheet that add time accured and subtracts time used... what I
need is for it to stop and not continue to add accured time if the max is
reached.

Thanks for any help offered.
 
If the accurals are in column A and the used are in column B:
=MIN(SUM(A1:A100)-SUM(B1:B100),1234)

where 1234 is the max
 
Roberta said:
I have a spreadsheet that add time accured and subtracts time used.
what I need is for it to stop and not continue to add accured time if the
max is reached.
Click to expand...

A concrete numerical example would help resolve ambiguities in the English
description.

Perhaps one of the following will work for you:

=min(--"12:30", sum(A1:A10)) - sum(B1:B10)

=min(--"12:30", sum(A1:A10) - sum(B1:B10))

In both case, the formula results in the lesser of 12h 30m and something
else. The "--" has the effect of converting the string to a time value.

This assumes that you have bona fide time values in A1:A10 (time accrued)
and B1:B10 (time used), not text. If you (temporarily) format the cells as
hh:mm, you should see the times that you expect.
 
Back
Top