The format is text in M21 the formula will not work unless it is an actual date. Test the cell and see if it is actually a date. I think you are importing this data from some other worksheet or program other than excel, the data may look like a date but in reality only be text. By changing the format to another date format mmm/dd/yyyy to m/d/yy for cell M21, if it does not change the way it is displayed it is not a (date) format, and when you manually enter the date you are then deleting the text data replacing it with a actual date. Temporarily widen the column it is in, it is aligns to the left it is not a date value, date values will align to the right of the column edge. Or another test you can do, in another open cell enter the formula " =ISNUMBER(M20)" it should return a 'TRUE" if it is a date and "FALSE" if it is something other. When importing a simple thing as a space can cause an error in the formula.