Sheet Automation with 3 conditions only respecting 1

DJM_0404

Hi Everyone - I'm trying to copy rows from one sheet to another with 3 conditions.

The first is when the "CF Primary" checkbox column is checked.

The second and third are if a project end date is greater than 12/31/23, OR if the project end date column is blank.

The rows that are getting copied over are only those that respect my first condition, when the "CF Primary" checkbox column is checked.

At first I created all three conditions under one condition block, and then split them out into two blocks, see screenshot.

What am I doing wrong? Appreciate your help!

Zachary Ziegler

I think the Greater Than field is reading your date as an integer, which doesn't apply to Dates. To verify that this is the logic gap, try to set the condition to:

Where KPHC Project End Date is between 12/21/23 and 12/21/30

That range should effectively give you everything past that date without fail unless you are planning more than 6 years out from now, but should clarify the condition check for you!

DJM_0404

Thanks Zachary, unfortunately that didn't work :( I still get rows pulled in from 2021 and 2023.

dojones

The problem is with the "is blank". This function returns an error when a date field is blank. You can get around this by setting up a helper field. Use this formula in the End Date Helper

=IFERROR(1 * [KPHC Project End Date]@row, 1)

Then in the automation, look for helper = 0 which will be a blank date.