Reply to thread

Message: [QUOTE="JoeU2004, post: 3839747"] Well, yes. Well, no. First, using a date (or any other non-Text) format only affects the appearance of __numeric__ values in a cell; and yes, it affects only their appearance, not their underlying value. But a numeric format has no effect on text in the cell. Nevertheless, that has nothing to do with why your original formula failed. Your original formula included the comparison C2<="31/03/2009". C2 is the cell that is presumably formatted as Date. And Excel correctly interpreted it as such. The issue is not with a cell's format, but with the interpretation of the text "31/03/2009". Excel does not interpret text as a number in a conditional comparison (!). But it does interpret text as a number in an arithmetic expression, when feasible. So "31/03/2009"+0 or --"31/03/2009" causes Excel to interpret the text as a date and replace it with a date serial number in the comparison. That allows for a proper comparison with the date serial number in C2, persumably. However, DATE(2009,3,31) is better because Excel will consider "31/03/2009" as mm/dd/yyyy or dd/mm/yyyy depending on the Regional and Language options on the computer the opens the Excel file. It might work fine on your computer, which presumably is set to dd/mm/yyyy; but it does not work on my computer, for example, which is set to mm/dd/yyyy. ----- original message ----- [/QUOTE]