IF And Formula Not Working

Ronald Anderson

I am trying to check a box when a date is between two dates. I have a seperate sheet with a start and end column dates. It keep erroring out.

=IF(AND([Punch Date]@row >= {StartDate}, [Punch Date]@row <= {EndDate}), 1, 0)

Thanks for your help,

Ron

Mike Wilday

What error are you getting? is there any way to send us an example or screenshot with dummy data?

Ronald Anderson

Here is the sample:

sharkasits

@Ronald Anderson is it always erroring or only when there isn't a match?

sharkasits

Actually I see the issue. Try this:

IF(COUNT(COLLECT({StartDate},{StartDate},<= [Punch Date]@row, {EndDate}, >=[Punch Date]@row))>0,1,0)

The reason your formula was giving you the error is because you were comparing a value to a range.

Ronald Anderson

Thank you for your response. Your suggestion does not error, but instead puts a 1 in each cell. In the example I provided the punch date does not fall within the two event start and end dates, so the formula should return 0.

Thank you,

Ron

Mike Wilday

I searched the error you're receiving and came across this:

https://help.smartsheet.com/articles/2476176-formula-error-messages#invalidoperation

However, I don't think it satisfies your issue. Does your cross-reference sheet target a single cell or a range of cells?

sharkasits

@Ronald Anderson It shouldn't be doing that... I tested it using one sheet and it worked.

Date 1 = Punch Date

Date 2 = Start Date

Date 3 = End Date

Formula in Column8:

Results:

Ronald Anderson

Could it be that I am using sheet references in the formula that is making it not work?

Regards,

Ron

Ronald Anderson

Mike Wilday - I references a range of cells.

Mike Wilday

That's your problem. The IF statement doesn't let you make a comparison against a range of data. It's a one for one... so you're going to have use something like VLOOKUP or COUNTIFS to find a matching date from the range of cells.

Try doing something like this: The first answer in this one has a simple COUNTIFS that is comparing the END and Start Date to a range of holidays and would meet your requirements I believe.

https://community.smartsheet.com/discussion/89889/can-i-determine-if-dates-fall-within-a-range-of-other-dates