Custom Cell Formatting Using % With Dates

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.

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Jason McDonald" wrote in message
...
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.

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

"Jason McDonald" wrote in message
...
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.