Formula to read "TRUE/FALSE" results from other formulas, issue with blank cells read as "TRUE" results for date comparison formula

[testermonkey]

I think I'm over-complicating a series of formulas I wrote to save time for myself.


My job is to e-mail the customer each week when Task A or Task B is completed on Projects A-F. The real spreadsheet has dozens of tasks and it's time-consuming to filter column-by-column for new dates, so I'm writing formulas to remove some of the labor.


See a simplified screenshot of a the spreadsheet I use and my columns with my formulas:

These are the formulas I've made in the above screenshot:

<TABLE><TR><TD><B>part of the spreadsheet</b></TD><TD>how i typed it</tD><TD><B>what it does</b></tD></TR><TR><TD>Cell B10</TD><tD>just typed a date</tD><TD>Contains the date of the last report I e-mailed to the customer</TD></TR>
<TR><TD>Column A</TD><TD>=AND((G2<>""), (G2>$B$10)) </TD><TD> I check if Task A has been completed since the date in B10. I test for blank cells ("G2<>"") because I was finding that I'd get false positives if I didn't eliminate blank cells.</TD></TR>
<tR><TD>Column B</TD><TD>=AND((H2>$B$10), (H2<>""))</TD><TD>I check if Task B has been completed since the date in B10 and for blankc ells, same as the other</TD></TR>

<tr><TD>Column C</TD><TD>=OR(D2=TRUE, E2=TRUE)</TD><TD>I check if there is a "TRUE" in columns D or E (meaning that the formulas in Columns A or B found a new date)</tD></TR>
<TR><TD>Columns D, E</TD><TD>Values from A, B pasted over as "values only"</TD><TD>This is something I feel has to have an answer in formulas. I found that the formula in column C couldn't find the "TRUE" or "FALSE" results of the column A and B formulas, so I copy and paste the results as values into D and E in the mean-time. </TD></TR>

<TR><TD>Columns F, G, H</TD><TD>Project information</TD><TD>These contain the project ID and the dates for the tasks, no formulas from me here</tD></TR>
</TABLE>

Afterwards, I send a cleaned-up spreadsheet to my customer filtered for rows where column C returns "TRUE."




I think I'm over-complicating this for myself. Is there a way I can eliminate the step where I paste the values into columns D and E? Do I use nested "IF" formulas? I googled this yesterday at the end of my shift but I think I was not using the correct terms because I didn't find relevant results.

 

[testermonkey]

I'm so frustrated by myself! I meant to make a nice HTML table to explain my screenshot but instead that showed up.

Cell B10: I type the date of the last report I e-mailed to the customer

Column A: Contains formula =AND((G2<>""), (G2>$B$10)) This checks if Task A has been completed since the date in B10. I test for blank cells ("G2<>"") because I was finding that I'd get false positives if I didn't eliminate blank cells.

Column B: Contains formula =AND((H2<>""), (H2>$B$10)) Same as column A, except it checks for Task B.

Column C: Contains formula =OR(D2=TRUE, E2=TRUE). I check if there is a "TRUE" in columns D or E (meaning that the formulas in Columns A or B found a new date.

Column D, E: Values from A, B are pasted over in these columns.I found that the formula in column C couldn't find the "TRUE" or "FALSE" results of the column A and B formulas, so I copy and paste the results as values into D and E in the mean-time.

Column F, G, H: Project information I reference for the formulas.

 

[testermonkey]

Addl note:
I'm using Excel as part of the Microsoft Office Professional Plus 2016 suite on Windows 10 Enterprise.

I apologize that I rushed into this post. I'm thoroughly embarrassed.