IF Function to return 1 or 0 based on date criteria.

Copper Contributor


I think I need an IF function to return either a 1 or 0 for the visit dates based on the start dates in column A, but maybe there is a better way? The start dates represent a change that would happen at the next visit, so I would need the visit on 11/30/20 to return a 1. The change that happened on 12/18/20 would return a 0 for 12/9/20, a 1 for 1/21/21, and so on. Do I need to write a nested IF function that reads longer than Crime & Punishment to make this work?

1 Reply

Hi @Sjbrinkley 


I think firstly the scenario is not well-defined. The end date is there but not used. Secondly the way that data is structured horizontally for the result data and vertically for the data is awkward. 


With above out of the way, instead of using IF, we can use something like =--(A7<B2) . The "<" will give us the True False that you are looking for. We can convert it to 1 and 0 with the double negative. 


Your result data is horizontal and lookup range is vertical. One way to solve this is use Transpose, something like =--(TRANSPOSE(A7:A10)<B2:E2)