Compare Dates, post status as text string

[williamsnow]

I am trying to jazz up a work status spreadsheet so that it automatically indicates whether a particular status "met expectations" or "achieved excellence" based on the date by which the task was completed.
I have one column for the "Meet Expectations" target date, one column for the "Achieve Excellence" target date, and one column for "Date Completed".

I am attempting to write a macro that will read the date completed, then compare it to the other two dates. If date complete is on or before Meet Expectations, but after Achieve Excellence, then the Results column should return "Met Expectations".

If date complete is on or before Achieve Excellence, then the Results column should return "Achieved Excellence".

I have written this so far:

Sub CalculateResults()
If (D20 <= C20) Then
F20 = "Achieved Excellence"
ElseIf (D20 > C20 And D20 <= B20) Then
F20 = "Met Expectations"
End If
End Sub

But it doesn't seem to be doing anything.

My file is attached.

Thanks,
Henry

[Arkadi]

Please use code tags as per forum rules.

That said, D20 in VBA is just a variable name... if you mean Cell D20's value then you refer to it as a range (and usually safest to include the sheet context also):

Please Login or Register  to view this content.

[nigelog]

One method

Please Login or Register  to view this content.

Add into a Module

[williamsnow]

Thank you! Helped me understand what I did not do right.

[williamsnow]

Awesome! Took the request to the logical conclusion to apply to all cells. Will study this to see what I didn't get right the first time.

[nigelog]

No problems, you might need some other form of error checking. This just moves to the next row if no date in column c. Ta for marking as solved