Excel Formula comparing dates

0
votes

I am trying to write an excel formula that checks to see if a finished date is the same as an expected finish date. If they are the same or the expected finish date is within 14 days of the finish date then 'Green' gets written in the cell, if not then the cell writes 'yellow'

This is my formula so far, but I am not getting the result that I am looking for. 

=IF((OR(M21 = N21,M21 <= M21 +14))"Green","Yellow")

M21 is a date written like: 2/4/2020 the same goes for N21

1
excelexcel-formula
M21 must be smaller than M21 +14. So why have that as a condition? unless it's a typo? - cybernetic.nomad
Welcome to SO. As mentioned by @cybernetic.nomad, it looks like there is a typo in your formula. M21 will always be less than (<) M21 + 14. However, with dates, they are notoriously problematic. Even with the cell format set to 'Date', formulas do not treat them as dates. I would ensure they are definitely dates by... Selecting the column >> Data >> Text to Columns >> 'Delimited' >> Next, uncheck all delimiters and set text qualifier to {none} >> Finish. This will ensure they are definitely dates. - GoodJuJu
@GoodJuJu Please avoid answering questions in comments. - teylyn

1 Answers

0
votes
=IF(N21 <= M21 +14,"Green","Yellow")

Should work fine.

If formulas with dates aren't working, its often because the dates have been stored as text. You can try editing them, or if too many or unclear, you can use the following:

=IF(DATEVALUE(N21) <= DATEVALUE(M21) +14,"Green","Yellow")