Checkbox based on Cross sheet Date reference

BBIrr41

Hello,

I am trying to have a checkbox in sheet A auto-check if the date in [Anticipated Transaction Date]@row falls between or is equal to the dates in sheet reference {Blackout Period Tracker Range 1} and sheet reference {Blackout Period Tracker Range 2}.

Help Please!

😅

BullandKhmer

=IF(AND([Anticipated Transaction Date]@row=>{Blackout Period Tracker Range 1},[Anticipated Transaction Date]@row=<{Blackout Period Tracker Range 2}),1)

BBIrr41

Thank you @BullandKhmer ! Unfortunately I am getting #INVALID OPERATION. ☹️

BullandKhmer

Yeah that's right. I made a basic syntax error. I'm sure you can figure out what it is.

BBIrr41

The syntax error I found didn't resolve the issue but thanks anyways.

Common issue: <= (less than or equal to) and >= (greater than or equal to) operator combinations in the wrong order.

Kelly Moore

Hey @BBIrr41

Are your Range 1 and Range 2 references referring to single cells each, or are both/either referring to a longer range (for instance, a column?)

BBIrr41

Hi @Kelly Moore ,

Range 1 and 2 are referring to a date columns. I have inserted a screenshot for reference.

Thank you!

Kelly Moore

Hey @BBIrr41

I thought that might be the issue. One has to construct IF statements differently when using a cross sheet range, if the range is greater than a single cell. As written, you are asking the IF to do a comparison against the entire column all at once and the formula fails this evaluation.

IF(COUNTIFS({Blackout Period Tracker Range 1},IFERROR(@cell,0)<=[Anticipated Transaction Date]@row, {Blackout Period Tracker Range 2},IFERROR(@cell,0)>=Anticipated Transaction Date]@row)>0,1)

Try this

Kelly

BBIrr41

Thank you @Kelly Moore ! That worked, one minor bracket missing but easy enough to find. I appreciate you not just helping, but giving me insight on the why. Have a great day!

IF(COUNTIFS({Blackout Period Tracker Range 1},IFERROR(@cell,0)<=[Anticipated Transaction Date]@row, {Blackout Period Tracker Range 2},IFERROR(@cell,0)>=[Anticipated Transaction Date]@row)>0,1)

Kelly Moore

Oops, good catch. My copy paste wasn't quite on point. I'm glad it worked.