Excel formula that returns a specific value based on if the date is before, during, or after a given date?

1
votes

I am trying to write an excel formula that returns a specific value based on if a given [month & year] falls before, between, and after a date range.

Screenshot

In the above screenshot, I have the date range: Column M - Column O. In subsequent columns (Column P - Column AC, etc.) is a given month & year.

  • If Column P - Column AC dates are less than Column M it needs to return Not Started.
  • If Column P - Column AC dates fall between the dates in Column M - Column O, it needs to return In Progress.
  • If Column P - Column AC dates fall after or equal to the dates in Column O, it needs to return Complete for all the following months.

What I am trying to do with this data is eventually make a historical trend chart that shows a count of each status by month & year, but I need to assign these statuses to each month first before I can do this. Any guidance is appreciated.

2
exceldateexcel-formula
What formulas have you attempted so far? A nested IF statement should work for your problem. - TotsieMae
So what happen if column O is blank? Complete or In Progress? - ian0411
It should be In Progress - G.Pa

2 Answers

0
votes

Use this formula =IF(C$1-$A2<0,"Not Started",IF(C$1-$B2<0,"In Progress","Completed")) in cell C2. Drag it across whole table. Make sure your dates are really dates and not text values.

enter image description here

0
votes

This is just a revised version of @KresimirL's answer:

=IF(C$1-$A2<0,"Not Started",IF(OR(C$1-$B2<0,$B2=""),"In Progress","Completed"))

This should work for you.