0

I have a cell (D1) in which a user can enter a date. I have a second cell (B1) that takes the date from cell D1 and adds 10 days to it and displays that date.

The user may not need to enter a date in cell D1. When they do not enter a date, Google sheets take the default date from cell D1 adds 10 and shows 1/9/1900. I would rather have it display N/A.

I entered this formula in cell B1.

=D1+10=IF(D1="12/30/1899","N/A","")

When there is no date in field D1, B1 displays FALSE. When there is a date entered in D1, the calculation does not happen.

Is the mixing of dates and text not allowed in the same cell? I have the cell formatted as "Automatic"

An example of what I am trying to do is here:

https://docs.google.com/spreadsheets/d/16o7uli52NCyFxrEWHvIbTq19Y9H8uld-s2GUrrRjUnY/edit?usp=sharing

0

Replace

=D1+10=IF(D1="12/30/1899","N/A","")

by

=IF(ISDATE(D1),D1+10,"N/A")

the above because :

  1. including a = between D1+10 and IF will compare the results of the parts
  2. Google Sheets, as other spreadsheet apps do, handle dates as serialized numbers displayed using number formatting settings, so comparing cell holding a "date" will never be equal to "12/30/1899".
2
  • It still comes up FALSE. I know the value in cell D1 must be 12/30/1899 because the + 10 value displays as 1/9/1900. Maybe something with the default date format?
    – user695293
    Oct 8 at 1:54
  • @user695293 Answer fixed.
    – Rubén
    Oct 8 at 2:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.