Negative time displayed as #######

[puiuluipui]

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!

 

[Luke M]

Change the cell's formatting. Either change it to something besides a time
format, or, go to custom format, and add on a message to display for negative
time.

So, let's say your current format is:
hh:mm:ss

Change this to:
hh:mm:ss;"Negative Time"

 

[Per Jessen]

Hi

To work with negative time, you have to change to '1904 date system'
or use this formula to show the message 'Less'

=if(IF(ISBLANK(D9)," ",ROUND(((D9-C9-"08:30")-1/288)/TIME(,30,),)*TIME
(,30,))<0,"Less",IF(ISBLANK(D9)," ",ROUND(((D9-C9-"08:30")-1/288)/TIME
(,30,),)*TIME(,30,))
)

Regards,
Per

 

[David Biddulph]

Or if you want to show the negative time as text, try changing Per's formula
to:
=IF(ISBLANK(D9),"",IF(ROUND(((D9-C9-"08:30")-1/288)/TIME(,30,),)*TIME(,30,)<0,"-"&TEXT(-ROUND(((D9-C9-"08:30")-1/288)/TIME(,30,),)*TIME(,30,),"hh:mm"),ROUND(((D9-C9-"08:30")-1/288)/TIME(,30,),)*TIME(,30,)))--David Biddulph"Per Jessen" <[email protected]> wrote in messageHi>> To work with negative time, you have to change to '1904 date system'> or use this formula to show the message 'Less'>> =if(IF(ISBLANK(D9)," ",ROUND(((D9-C9-"08:30")-1/288)/TIME(,30,),)*TIME> (,30,))<0,"Less",IF(ISBLANK(D9)," ",ROUND(((D9-C9-"08:30")-1/288)/TIME> (,30,),)*TIME(,30,))> )>> Regards,> Per>>> On 14 Dec., 15:40, puiuluipui <[email protected]>> wrote:>> 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!>

 

[Joe User]

=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]

Hi Joe, thanks for the complex answer. Your ideeas for negative number were
all good. Thanks. But can you help me with the original formula? I need a
formula to calculate time from 30 to 30 minutes and to round the result with
10 minutes. Your

Ex:
Work hour : 08:30:00
08:30 - 17:20 overtime = 00:20 result(00:30)
08:30 - 17:19 overtime = 00:19 result(00:00)
08:30 - 17:50 overtime = 00:50 result(01:00)
08:30 - 17:49 overtime = 00:30 result(00:30)

The result time from "hh:20" to "hh:30" to be "hh:30"
The result time from "hh:50" to "hh:00"(or hh:59:59) to be hh:00 (full hour)

Something like: (A1-B1)-"08:30",and then to calculate from 30 to 30 min, and
round the result with 10 minutes.
I hope you have a better ideea than the formula below.

Thanks!

"Joe User" a scris:

=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 -----

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!

.

 

[Joe User]

can you help me with the original formula?
I need a formula to calculate time from 30 to 30 minutes
and to round the result with 10 minutes.

I am not sure I can. I have no idea what "from 30 to 30 minutes" and "round
.... with 10 minutes" mean.

The result time from "hh:20" to "hh:30" to be "hh:30"
The result time from "hh:50" to "hh:00"(or hh:59:59)
to be hh:00 (full hour)

Based on your examples and that description, I think (but I am not sure at
all) that you are trying to say:

(a) Calculate overtime beyond elapsed time of 8h 30m; and

(b) "Round" overtime according to following conventions: round to h:00 if
overtime is less than h:20; round to h:30 if overtime is at least h:20 and
less than h:50; and round to (h+1):00 if overtime is h:50 or more.

08:30 - 17:49 overtime = 00:30 result(00:30) [....]
Something like: (A1-B1)-"08:30"

If A1 is 08:30 and B1 is 17:49, I assume you meant to say: something like
(B1-A1)-"08:30".

Question: how to do you want to interpret the following? My guesses:

08:30 - 16:30 overtime: 0:00 result: 0:00 (Or overtime: -0:30?!)

22:00 - 8:30 overtime: 2:30 result: 0:00 (Recognizing the normal 8h
30m shift from 10:00pm to 6:30am?)

It would be easier if we calculate unrounded overtime in a cell, say C1,
then calculate the "rounded" overtime in D1. Namely:

C1:
=max(0, (B1<A1) + B1 - A1 - "08:30")

D1:
=if(minute(C1)<20, time(hour(c1),0,0),
if(minute(C1)>=50, time(1+hour(c1),0,0), time(hour(c1),30,0)))

Both C1 and D1 should be formatted as Custom [h]:mm.

The formula in C1 relies on the fact that time is stored as a fraction of a
day. So 1 (B1<A1) represents 24 hours.

Of course, you could replace every C1 in D1 with
MAX(0,(B1<A1)+B1-A1-"08:30") if you want a single formula. But that is
messy and inefficient.

If these formulas do not do the computation you had in mind, please provide
some examples that demonstrate their failure, together with the desired
result and an explanation of the interpretation.


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

Hi Joe, thanks for the complex answer. Your ideeas for negative number
were
all good. Thanks. But can you help me with the original formula? I need a
formula to calculate time from 30 to 30 minutes and to round the result
with
10 minutes. Your

Ex:
Work hour : 08:30:00
08:30 - 17:20 overtime = 00:20 result(00:30)
08:30 - 17:19 overtime = 00:19 result(00:00)
08:30 - 17:50 overtime = 00:50 result(01:00)
08:30 - 17:49 overtime = 00:30 result(00:30)

The result time from "hh:20" to "hh:30" to be "hh:30"
The result time from "hh:50" to "hh:00"(or hh:59:59) to be hh:00 (full
hour)

Something like: (A1-B1)-"08:30",and then to calculate from 30 to 30 min,
and
round the result with 10 minutes.
I hope you have a better ideea than the formula below.

Thanks!

"Joe User" a scris:

=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 -----

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!

.

 

[puiuluipui]

Hi Joe, sorry for my late reply.

This is a link to a prior post from where i have this code and where you can
find more explications
http://www.microsoft.com/office/com...&p=1&tid=d2ccc2ea-f476-4397-91cb-4ee22cadf686

Thanks allot!





Joe User" a scris:

can you help me with the original formula?
I need a formula to calculate time from 30 to 30 minutes
and to round the result with 10 minutes.

I am not sure I can. I have no idea what "from 30 to 30 minutes" and "round
.... with 10 minutes" mean.

The result time from "hh:20" to "hh:30" to be "hh:30"
The result time from "hh:50" to "hh:00"(or hh:59:59)
to be hh:00 (full hour)

Based on your examples and that description, I think (but I am not sure at
all) that you are trying to say:

(a) Calculate overtime beyond elapsed time of 8h 30m; and

(b) "Round" overtime according to following conventions: round to h:00 if
overtime is less than h:20; round to h:30 if overtime is at least h:20 and
less than h:50; and round to (h+1):00 if overtime is h:50 or more.

08:30 - 17:49 overtime = 00:30 result(00:30) [....]
Something like: (A1-B1)-"08:30"

If A1 is 08:30 and B1 is 17:49, I assume you meant to say: something like
(B1-A1)-"08:30".

Question: how to do you want to interpret the following? My guesses:

08:30 - 16:30 overtime: 0:00 result: 0:00 (Or overtime: -0:30?!)

22:00 - 8:30 overtime: 2:30 result: 0:00 (Recognizing the normal 8h
30m shift from 10:00pm to 6:30am?)

It would be easier if we calculate unrounded overtime in a cell, say C1,
then calculate the "rounded" overtime in D1. Namely:

C1:
=max(0, (B1<A1) + B1 - A1 - "08:30")

D1:
=if(minute(C1)<20, time(hour(c1),0,0),
if(minute(C1)>=50, time(1+hour(c1),0,0), time(hour(c1),30,0)))

Both C1 and D1 should be formatted as Custom [h]:mm.

The formula in C1 relies on the fact that time is stored as a fraction of a
day. So 1 (B1<A1) represents 24 hours.

Of course, you could replace every C1 in D1 with
MAX(0,(B1<A1)+B1-A1-"08:30") if you want a single formula. But that is
messy and inefficient.

If these formulas do not do the computation you had in mind, please provide
some examples that demonstrate their failure, together with the desired
result and an explanation of the interpretation.


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

Hi Joe, thanks for the complex answer. Your ideeas for negative number
were
all good. Thanks. But can you help me with the original formula? I need a
formula to calculate time from 30 to 30 minutes and to round the result
with
10 minutes. Your

Ex:
Work hour : 08:30:00
08:30 - 17:20 overtime = 00:20 result(00:30)
08:30 - 17:19 overtime = 00:19 result(00:00)
08:30 - 17:50 overtime = 00:50 result(01:00)
08:30 - 17:49 overtime = 00:30 result(00:30)

The result time from "hh:20" to "hh:30" to be "hh:30"
The result time from "hh:50" to "hh:00"(or hh:59:59) to be hh:00 (full
hour)

Something like: (A1-B1)-"08:30",and then to calculate from 30 to 30 min,
and
round the result with 10 minutes.
I hope you have a better ideea than the formula below.

Thanks!

"Joe User" a scris:

=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 -----

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!

.

.

 

[Per Jessen]

Hi

Try this formula with start time in A2 and end time end B2:

=IF((B2-A2)<TIME(8,50,0),0,IF((B2-A2)-TIME(8,30,0)>=TIME(0,49,59),TIME
(1,0,0)-(B2-A2-TIME(8,30,0))+B2-A2-TIME(8,30,0),TIME(0,30,0)-((B2-A2)-
TIME(8,30,0))+(B2-A2)-TIME(8,30,0)))

Regards,
Per

Hi Joe, sorry for my late reply.

This is a link to a prior post from where i have this code and where you can
find more explications.http://www.microsoft.com/office/community/ro-ro/default.mspx?&lang=en...

Thanks allot!

Joe User" a scris:

I am not sure I can.  I have no idea what "from 30 to 30 minutes" and"round
.... with 10 minutes" mean.

Based on your examples and that description, I think (but I am not sureat
all) that you are trying to say:

(a) Calculate overtime beyond elapsed time of 8h 30m; and

(b) "Round" overtime according to following conventions:  round to h:00 if
overtime is less than h:20; round to h:30 if overtime is at least h:20 and
less than h:50; and round to (h+1):00 if overtime is h:50 or more.

08:30 - 17:49   overtime = 00:30  result(00:30) [....]
Something like: (A1-B1)-"08:30"

If A1 is 08:30 and B1 is 17:49, I assume you meant to say:  somethinglike
(B1-A1)-"08:30".

Question:  how to do you want to interpret the following?  My guesses:

08:30 - 16:30   overtime: 0:00  result: 0:00   (Or overtime:  -0:30?!)

22:00 -   8:30   overtime: 2:30  result: 0:00   (Recognizing the normal 8h
30m shift from 10:00pm to 6:30am?)

It would be easier if we calculate unrounded overtime in a cell, say C1,
then calculate the "rounded" overtime in D1.  Namely:

C1:
=max(0, (B1<A1) + B1 - A1 - "08:30")

D1:
=if(minute(C1)<20, time(hour(c1),0,0),
if(minute(C1)>=50, time(1+hour(c1),0,0), time(hour(c1),30,0)))

Both C1 and D1 should be formatted as Custom [h]:mm.

The formula in C1 relies on the fact that time is stored as a fraction of a
day.  So 1 (B1<A1) represents 24 hours.

Of course, you could replace every C1 in D1 with
MAX(0,(B1<A1)+B1-A1-"08:30") if you want a single formula.  But that is
messy and inefficient.

If these formulas do not do the computation you had in mind, please provide
some examples that demonstrate their failure, together with the desired
result and an explanation of the interpretation.

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

Hi Joe, thanks for the complex answer. Your ideeas for negative number
were
all good. Thanks. But can you help me with the original formula? I need a
formula to calculate time from 30 to 30 minutes and to round the result
with
10 minutes. Your
Ex:
Work hour : 08:30:00
08:30 - 17:20  overtime = 00:20  result(00:30)
08:30 - 17:19   overtime = 00:19  result(00:00)
08:30 - 17:50   overtime = 00:50  result(01:00)
08:30 - 17:49   overtime = 00:30  result(00:30)
The result time from "hh:20" to "hh:30" to be "hh:30"
The result time from "hh:50" to "hh:00"(or hh:59:59) to be hh:00 (full
hour)
Something like: (A1-B1)-"08:30",and then to calculate from 30 to 30 min,
and
round the result with 10 minutes.
I hope you have a better ideea than the formula below.
Thanks!
"Joe User" a scris:
=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.  Thatmay
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 -----
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 timeor
to
display a message instead of "#########"?
The message to be "Less"
Can this be done?
Thanks!
.

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -