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
What error are you getting? is there any way to send us an example or screenshot with dummy data?
Here is the sample:
@Ronald Anderson is it always erroring or only when there isn't a match?
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.
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
I searched the error you're receiving and came across this:
However, I don't think it satisfies your issue. Does your cross-reference sheet target a single cell or a range of cells?
@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:
Could it be that I am using sheet references in the formula that is making it not work?
Regards,
Ron
Mike Wilday - I references a range of cells.
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.
