"puiuluipui" wrote:
=IF(ISBLANK(D9)," ",ROUND(((D9-C9-"08:30")-1/288)/TIME(,30,),)*TIME(,30,))
[....]
Is there a way that this formula to display even the negative time [...]?

This would be easier to do with a helper cell. In X9, formatted as General:

=IF(D9="", "", ROUND((D9-C9-"08:30"-1/288)/"00:30",0)*"00:30")

Then where you want the original formula:

=IF(D9="", "", IF(X9<0,"-","") & TEXT(ABS(X9),"hh:mm"))

formatted with Right alignment.

Of course, you could eschew the helper cell if you use a more complicated
formula, to wit:

=IF(D9="", "", IF(D9-C9-"08:30"-1/288 < 0, "-", "")
& TEXT(ABS(ROUND((D9-C9-"08:30"-1/288)/"00:30",0)*"00:30"), "hh:mm")

Note that the result of these formulas is text, not numeric. If you want
numeric, I think you will need two cells: one with your original formula
formatted as General, which you can reference in other computations; and the
other a TEXT formula like one of those above, which you can use for display
purposes.

Or simply change the format to Custom [h]:mm;"Less" . The underlying cell
value will still remain negative time.


Additional comments:

1. Write "" instead of " "; that is, no space between double-quotes.
Otherwise, you will make it difficult to recognize cells that __appear__
empty. See #2.

2. Use D9="" instead of ISBLANK(D9). That recognizes cells that __appear__
empty, whether they are truly empty cells (no formula and no constant), or
they have formulas that might return null strings ("") like yours does.
Note that ISBLANK is a misnomer; it is true only if the cell has no formula
and no constant.

3. Do yourself a favor and resist any suggestion to change the date option
to "1904 date system" just so you can display negative time. That may have
other untoward consequences, if you are not careful.

4. Why write 1/288? If your intention is to subtract 5 min, why not write
TIME(0,5,0) or "00:05"? In fact, why not change -"08:30"-1/288 to
simply -"08:35"? Rhetorical questions; just something for you to think
about and answer for yourself.


----- original message -----

"puiuluipui" wrote in message
...
Hi, i have this code:
=IF(ISBLANK(D9)," ",ROUND(((D9-C9-"08:30")-1/288)/TIME(,30,),)*TIME(,30,))

If the result is a negative time, then the formula display "###########".
Is there a way that this formula to display even the negative time or to
display a message instead of "#########"?
The message to be "Less"

Can this be done?
Thanks!