Combining =IF & ISBLANK

[Omniflo]

Hey there,

I am wondering if anyone can assist with the below problem.
In Cell A2 I have an instruction date e.g. 16/06/2012, in Cell B2 I have an end date(or the cell is blank if there is no end date), in Cell C3 I have the formula =IF(ISBLANK(B2),NOW()-A2,B2-A2) this calculates the number of days between the start date and end date (and uses todays date if B2 is blank).

I am looking for assistance with a formula for cell D2 that would return the following:
If C2>42 "Missed"
If C2<=42 "On Target"
If B2 is blank always return "In Progress" regardless of the figure in C2
If A2 is blank always return "Not instructed"

I hope this explains whats I am trying to solve in an understandable way, and thank you in advance if you are able to assist.

-Omniflo

[dilipandey]

Hi Omniflo,

Welcome to the forum.

May be below one can help you:-

Please Login or Register  to view this content.

Regards,
DILIPandey

<click on below 'star' if this helps>

[crispybadger]

Hi,

Dillipandey's formula works perfect, but im getting confused with "and uses todays date if B2 is blank"?

I just replaced your original formula in C2 with =IF(B2="","",IF(A2="","",B2-A2)) Just seemed to tidy it up a bit and worked perfect with Dilipandey's formula

Cheers

[dilipandey]

Cheers

Please mark this thread as [solved].. THANKS.

Regards,
DILIPandey

<click on below 'star' if this helps>

[crispybadger]

Cheers

Please mark this thread as [solved].. THANKS.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Dilipandey,

I was just helping the OP. Their formula was floored I felt in C2, so I just added some advice, on the back of your original reply

Its down to Omniflo to change the thread status.

Cheers

[dilipandey]

Okay.. I missed to see the OP's name

Regards,
DILIPandey

<click on below 'star' if this helps>

[Omniflo]

Hey DILIPandey,

Thank you for the welcome & the assistance it worked perfectly, and also thank you to crispybadger as your formula did tidy it up nicely!

Thanks - Omniflo.

[dilipandey]

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>