Date handling

[jonnywakey]

Hi wondering if anyone can help me with what should be a simple formula but beyond me at the moment

I have a cell lets say A1 with =today() which is fine

Cell A2 contains =text(A1,"mmm-yy") which is fine to show current month in desired format

Cell A3 contains =TEXT(TODAY()-DAY(TODAY()),"mmm-yy") which is fine to show month prior to current in desired format


What i need to achieve is on a tracker (same worksheet) i have a planned finish date in cell H4 in following format "mmm-yy", i would like a cell next to (I4) it which does the following:-


If H4 = A2 (current month) then text "Due this month" should appear in cell I4

If H4 <= A3 (previous month) then text "Overdue" should appear in cell I4

If H4 >A2 (current month) then text "On Track" should appear in cell I4

If H4 is blank or any other value then "Enter PFD" should appear in cell I4

Hope this makes sense and someone can assist.

Much appreciated

Jonny

[PaulM100]

=If(H4 = A2,"Due this month",If(H4 <= A3,"Overdue",If(H4 >A2 ,"On Track","Enter PFD")))

[Roel Jongman]

your text explanation is not enough to help you solve..
So you have formula's that convert dates to text. The value in H4 is that a text as well?

- if H4 is text you cannot compare texts as greater than or less than..
- if H4 is a true datevalue you cannot compare it to dates presented as texts in A2 and A3

Also is your current version still excel 2003? because if you use a later version then there are also some other dates formulas that can be used to solve your question
To help you get to the desired end result we need an example file because the way the formula needs to be build depends on the true formatting of the text.

Please see the yellow banner at the top on how to upload an example file..

[Bassehave]

If you want to skip the formatting steps and keep the one reference cell, you can use this formula.

Site is blocking my post, so I'm adding it as a picture.

a.PNG