Forum Discussion
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 the data in the 2nd cell with for example TODAY(), this data is now recognized in a date format and my formula works. How do I force the data in 2nd cell to look like date format. I have formatted this cell repeatedly with Format Date "Day, Month, Year" but no worky. ??
2 Replies
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
Ok thanks Haytham. Turns out the Date Function must be a Whole Number, no fractions, which is what was causing no result.
Thanks again.
Bill
