Forum Discussion
Project Plan (If Formula) Query
Hello, I am trying to create a project plan on Excel. Currently the formula in N6 is simply ‘=L6+M6’ and N7 is the same methodology. The issue with that is the end date will show up as 00/01/1900 if column L and M are blank. But column O onwards are fine in this scenario and work as expected.
To mitigate this issue in N8 and N10 I added this formula ‘=IF(ISBLANK(L8), "",L8+M8)’ Again this works fine if column L and M have data to fill in column N. However, if I leave N10 blank; everything on column O onwards is filled in when I want it to be blank. The formula in O10 onwards follows the pattern of ‘=IF(AND(O$5>=$L10,O$5<=$N10),"X","")’ Is anyone able to suggest a formula for either column N or column O onwards which can mitigate these issues?
I've added a screenshot for context of the spreadsheet.
Thank you! 🙂
Wll2546 Let's start with the first question first. Dates are stores as sequential numbers, starting at January 1, 1900. When you format a column as Date, a zero will, oddly enough, become 0 January 1900. You can suppress that by custom formatting the entire date column with the desired date format followed by two semicolons. See picture below where the active cell contains a zero, formatted as a custom date with ;;
And this works in column O (and onwards) because zero never falls within any of the date ranges, and most likely, the conditional formatting condition that colors the cells green is never met. This will thus solve the problem you raised in the second question, which is most likely caused by the conditional formatting rule that colors the cells green. It is probably based =NOT(ISBLANK(cell)) . A cell with "" is not considered empty/blank when you evaluate it with ISBLANK. You would need a rule like =cell<>""
1 Reply
Wll2546 Let's start with the first question first. Dates are stores as sequential numbers, starting at January 1, 1900. When you format a column as Date, a zero will, oddly enough, become 0 January 1900. You can suppress that by custom formatting the entire date column with the desired date format followed by two semicolons. See picture below where the active cell contains a zero, formatted as a custom date with ;;
And this works in column O (and onwards) because zero never falls within any of the date ranges, and most likely, the conditional formatting condition that colors the cells green is never met. This will thus solve the problem you raised in the second question, which is most likely caused by the conditional formatting rule that colors the cells green. It is probably based =NOT(ISBLANK(cell)) . A cell with "" is not considered empty/blank when you evaluate it with ISBLANK. You would need a rule like =cell<>""