Forum Discussion
Formatting default 21/01/1900 date
I have a sheet where it is formatted with the below formulas. When a sent date is to be completed within 7 days the text turns red, when it is to be completed within 14, the text turns amber. However...
Change the formula in B2 to
=IF(A2,A2+21,"").... and it shall work.
Your current formula returns then number 21 when A is empty. Formatted as a date will display it as the 21st day in Excels calendar that starts on January 1, 1900.
This very basic formula may still give unwanted results if you enter text in column A or numbers that translate to dates long into the past or into the future. You could capture such errors by setting up data validation for the cells in column a to only accept date in a certain range. I did not go that far in the attached sheet.