Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative time displayed as #######
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative time displayed as #######
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" -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "puiuluipui" 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative time displayed as #######
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 On 14 Dec., 15:40, puiuluipui 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative time displayed as #######
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" wrote in ... 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 On 14 Dec., 15:40, puiuluipui 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative time displayed as #######
"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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative time displayed as #######
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: "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! . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative time displayed as #######
"puiuluipui" wrote:
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 ----- "puiuluipui" wrote in 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: "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! . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative time displayed as #######
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/comm...b-4ee22cadf686 Thanks allot! Joe User" a scris: "puiuluipui" wrote: 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 ----- "puiuluipui" wrote in 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: "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! . . |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative time displayed as #######
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 On 18 Dec., 10:38, puiuluipui wrote: 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/comm....mspx?&lang=en... Thanks allot! Joe User" a scris: "puiuluipui" wrote: 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 ----- "puiuluipui" wrote in 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: "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! . .- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Negative times are displayed a ########### | Excel Discussion (Misc queries) | |||
Zero displayed in place of negative values | Excel Discussion (Misc queries) | |||
negative dates or times are displayed as # # # # | Excel Worksheet Functions | |||
negative dates or times are displayed as # # # # | New Users to Excel | |||
Negative Times Displayed as # # # # | Excel Discussion (Misc queries) |