Nested IF ANDs and ORs to enter in a date for check in and out

keciaheld

I am having difficulty writing a formula to enter in a date for check in based on the days that staff will be at a conference. I have check boxes for the days that people will be at the conference and I wanted to have the check in/out dates autopopulate based on that information. I am able to get the first couple columns to work but when I try to add more the logic breaks. Here is what I got to work:

=IF([4/28]@row = 1, "4/27/24", (IF(AND([4/28]@row = 0, [4/29]@row = 1), "4/28/24")))

I tried doing it like this and with a leading OR but I cant get it to work:

=IF([4/28]@row = 1,"4/27/24",IF(OR([4/28]@row = 0, [4/29]@row = 1), "4/28/24",(IF(AND([4/29]@row = 0, [4/30]@row= 1), “4/29/24”), (IF(AND([4/30]@row = 0, [5/1]@row=1),”4/30/24”), (IF(AND([5/1]@row=0,[5/2]@row=1),”5/1/24”),(IF(AND([5/2]@row=0,[5/3]@row=1),”5/2/24”), (IF(AND([5/3]@row=0,[5/4]@row=1),”5/3/24”)))

smartsheet dates.jpg

keciaheld

I got it to work - thanks for the input!

=IF([4/28]@row = 1, "04/27/24", IF(AND([4/29]@row = 1, [4/28]@row = 0), "04/28/24", IF(AND([4/29]@row = 0, [4/30]@row = 1), "04/29/24", IF(AND([4/30]@row = 0, [5/1]@row = 1), "04/30/24", IF(AND([5/1]@row = 0, [5/2]@row = 1), "05/01/24", IF(AND([5/2]@row = 0, [5/3]@row = 1), "05/02/24"))))))

Paul Newcome

It looks like this thread may have a solution for you:

https://community.smartsheet.com/discussion/comment/393539#Comment_393539

https://community.smartsheet.com/discussion/comment/393539#Comment_393539

keciaheld

I got it to work - thanks for the input!

=IF([4/28]@row = 1, "04/27/24", IF(AND([4/29]@row = 1, [4/28]@row = 0), "04/28/24", IF(AND([4/29]@row = 0, [4/30]@row = 1), "04/29/24", IF(AND([4/30]@row = 0, [5/1]@row = 1), "04/30/24", IF(AND([5/1]@row = 0, [5/2]@row = 1), "05/01/24", IF(AND([5/2]@row = 0, [5/3]@row = 1), "05/02/24"))))))