Here is what I am trying to do. I am entering a list of leaves in one sheet with To and From date values. Now given a date I want to find out whether there is a leave on that date. How can I do that?
For instance, I add following data in excel
Jack | 1-Jan-2014 | 3-Jan-2014
Jill | 15-Jan-2014 | 15-Jan-2014
Now I want to check whether Jill is on leave on 2nd Jan, how can I write a formula to check all the date ranges in two columns?
Now I am now trying to write a custom function to do that. But want to know whether we can do that OOTB.
Use COUNTIFS. This:
=COUNTIFS($B:$B,"<="&D2,$C:$C,">="&D2)
will tell you how many leaves contain the date in D2, where leaves start in column B and end in column C.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments