Custom Cell Formatting Using % With Dates

[Jason McDonald]

First, thanks in advance for any help.

I'm using the NETWORKDAYS function along with the TODAY function to
calculate the amount of time that has elapsed for a given phase on a
function. The problem I have is with formatting the percentage
complete. So for example...

1. Today is 3/10/10. If the phase of the project is say 3/5/10 to
3/12/10 I'm fine.
2. If the start date is later, say 3/11/10 it displays a negative
number in the cell, like -10% complete.
3. If the completion date was 3/9/10 I might end up with something
like 110% complete.

Now, I've been able to fix this by custom formatting the cells by
using something like this:
0%;[<0]"0%";General to get rid of the negatives OR [>1]"100%";General
BUT
when I try to put them together in one logical formula I always end up
screwing something up and it doesn't come out right.

Again, appreciate your help.

 

[Bernard Liengme]

Would you like to share your formula that computed the percentage?
Maybe this can be fixed to give a blank rather than negative value
best wishes

 

[Fred Smith]

Try the following custom format:
[<0]"0%";[>1]"100%";0%

Remember, a format affects only the display of the number. If you use the
cell in another formula, the actual value (like -10%) will be used, not the
displayed result.

Regards,
Fred