If there is a date in the column return "Complete"

[TYOzzy]

Hello,

I'm trying to come up with a formula to put in cell K2 that says, if there is a date (any date at all) in cell J2 then return "Complete" in cell K2, but if there is no date in cell J2, subtract today's date from cell I2 and place the value in work days in cell K2.

I've tried =IF(J2>=I2,"Complete",TODAY()-I2) , but it returns the word complete even if there is a blank in J2. It also doesn't count for workdays.

Thanks in advance for your help!
TYOzzy

 

[jeffreybrown]

Could something like this work for you...

=IF(N(J2),"Complete",NETWORKDAYS(I2,TODAY()))

 

[TYOzzy]

Could something like this work for you...

=IF(N(J2),"Complete",NETWORKDAYS(I2,TODAY()))

Works beautifully. Thanks so much! What does the N do for the formula? I appreciate it greatly.

 

[jeffreybrown]

Take a look in the Excel help which will explain in far better than I, but in essence, the N function is looking for a number and not text.

You could also use...

=IF(ISNUMBER(J2),"Complete",NETWORKDAYS(I2,TODAY()))

 

[TYOzzy]

Could something like this work for you...

=IF(N(J2),"Complete",NETWORKDAYS(I2,TODAY()))

Out of curiosity... Is there a way to get it to calculate the months and leftover days instead of all days? Thanks again! It's not necessary, I'm just curious.

 

[TYOzzy]

Take a look in the Excel help which will explain in far better than I, but in essence, the N function is looking for a number and not text.

You could also use...

=IF(ISNUMBER(J2),"Complete",NETWORKDAYS(I2,TODAY()))

Ahh thank you. I knew about the "isnumber", but not the N. I fought with the formula for a long time before I gave up and came to the forums.