Checkbox with IF AND formula- satisfy two criteria simultaneously

Shuai Yao

Hi,

I want to add a formula to a checkbox column, the purpose is to automatically check the box once the cell satisfies two criteria simultaneously.

Criteria 1: Phase column=Milestone

Criteria 2: Finish Date column<= 03/11/2024

I have tried a few IF AND formulas but still can't get the result desired, loosing temper now :(

This is my current formula:

=IF(AND(Phase@row = "Milestone", Finish@row <= "03/11/24"), "yes", "no")

Please help thank you!

heyjay

How about this?

=IF(AND(
Phase@row = "Milestone", 
Finish@row <= DATE(2024, 3, 11)), 1, 0)

heyjay

=IF(AND(
Phase@row = "Milestone", 
Finish@row <= date(2024, 3,11), "1", "0")

1 and 0 are the switches for checkboxes.

For DATEs, use the function DATE (YYYY,MM,DD)

Shuai Yao

https://community.smartsheet.com/discussion/comment/416363#Comment_416363

Thanks!!! I used 1,0 too but it didn't work, I just copied your formula to the column, it shows INVALID OPERATION

heyjay

How about this?

=IF(AND(
Phase@row = "Milestone", 
Finish@row <= DATE(2024, 3, 11)), 1, 0)

Shuai Yao

https://community.smartsheet.com/discussion/comment/416373#Comment_416373

OMG, you made my day. Thank you!!!!It worked!