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 when typing in any row, the text always appears as red and the column shows the default as 21/01/1990 when dragging the formula down.
Is there a way to have the default text black unless those ranges are met and to have the columns appear as blank rather than with the default 21/01/1900 format date?
Formulas used are:
=$A2-TODAY()<=7
=$A2-TODAY()<=14
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.
