Returning values in cell dependent on date values in two different cells

[bazhsw]

I have an Excel 2013 document where I am getting a little confused with IF and AND functions. My problem seems relatively simple to resolve but I am having a few challenges!

I have three columns which are affected.

In Column A there is a deadline date
In Column B there is a completed date
In Column C there is a Status field

I have three conditions that need to return a value;

a) If any date is entered into the Column B I would like a value to be returned in column C of 'Complete'.
b) If no date is entered into Column B (the cell is empty) and if the date in Column A is after the current date I would like a value to be returned in Column C of 'overdue'
c) If no date is entered into Column B (the cell is empty) and if the date in Column A is equal to or before the current date I would like a value to be returned in Column C of 'in progress'

My issue seems to be combining IF functions but also combining the three conditions, especially b and c which I think are actually two logical tests wrapped up into one. I'm sure the answer is staring back at me!

Thanks for your help

 

[Joyner]

Hello and welcome to the board, this is untested, but try this:

IF(AND(B1="",A1<=TODAY()),"in progress", IF(AND(B2="",A1>TODAY()),"overdue",IF(ISNUMBER(B1),"Complete","")))

If you need something that determines the value in A1 and B1 can be a number something OTHER than a date, we may need to change to look at A1 and B1 different.

 

[bazhsw]

Joyner - thank you for your welcome and especially thank you for your advice. This works perfectly - thank you so much for your help and quick response!

 

[Joyner]

You are welcome