Help required with Date Function

I have a date in one cell, referring to it from a 2nd cell to produce another date. Reviewing the 2nd cell with an IF(AND( formula does not recognize the data in the 2nd cell as a date. If I replace ...

Formulas and Functions

Haytham Amairah

Silver Contributor

Mar 12, 2018

Hi Bill,

Please take into account the below info.

Dates in the Excel file must be compatible with the date format in the operating system, if the original format of the dates is different from the local date format, problems with dates will appear, they may be treated as text, or they may remain formatted as dates, but not as you think it.

Examples:

Local Date is: (M/d/yyyy)

Excel Date is: 15/12/2017

This date will be treated as text because there is no month 15!

Local Date is: (M/d/yyyy)

Excel Date is: 1/12/2017

This date will still formatted as Date in Excel, but not the date you may want.

You may think it 1/Dec/2017, but actually, it 12/Jan/2017 as the local date format!

Please make sure that the format of the dates in the Excel sheet is compatible with your local date format.

Hope that helps

Haytham