Formula help

[bailbonds]

Hi,

I have 3 cells

W5 = a date
X5 = a date
Y5 = will be a calc to show either W5 or X5 minus todays date

My stipulations are -

If W5 and X5 are blank then Y5 must be blank
If W5 and X5 have a value then Y5 must be the calc showing X5 minus today's date
If X5 is blank but W5 has a value then Y5 must be the calc showing W5 minus today's date

I can get so far but I can't get it to do the calc if W5 and X5 have a value.

My formula so far is -

=IF(AND(W5="",X5=""),"",IF(X5="",NETWORKDAYS(Sheet2!B1,W5,0),IF(W5="",NETWORKDAYS(Sheet2!B1,X5,0),"")))

Where 'Sheet2!B1' is the cell that references today's date.

Any ideas?

Thanks in advance.

 

[nickshep85]

How about...

Put this in Y5

=IF(AND(W5="",X5=""),"",IF(AND(W5<>"",X5<>""),X5-TODAY(),IF(AND(X5="",W5<>""),W5-TODAY())))

 

[duyanzhang]

=IF(AND(W5="",X5=""),"",IF(X5="",NETWORKDAYS(Sheet2!B1,W5,0),IF(W5="",NETWORKDAYS(Sheet2!B1,X5,0),NETWORKDAYS(Sheet2!B1,X5,0))))
TRY?

 

[puru.sve]

Hi ,

You can try this

=IF(AND(W5="",X5=""),"",IF(W5<>"",IF(X5<>"",NETWORKDAYS(X5,TODAY()),NETWORKDAYS(W5,TODAY())),IF(X5="","",NETWORKDAYS(X5,TODAY()))))

 

[bailbonds]

Cheers guys.

I've tried both and messed about a bit but they both return True or False numbers.

I then made a minor tweak to my original one by changing it to -

=IF(AND(W5="",X5=""),"",IF(X5="",NETWORKDAYS(Sheet2!B1,W5,0),IF(W5>1,NETWORKDAYS(Sheet2!B1,X5,0),"")))

I thought this originally worked but am now getting True or False returns on this.

Any ideas why?

 

[mikerickson]

How about
=IF(COUNTA(W5:X5), X5+(X5="")*W5-TODAY(), "")