Match A Date Within A Date Range In Another Sheet
Please can somebody help me.
I have a workbook that contains 2 sheets.
In sheet1 and column A there is a date and time and in sheet2 columns A and B a from and to date and time.
There are a number of rows in each sheet.
I would like a formula that searches sheet2 and puts a 1 in sheet1 column B where the value finds a match between the to and from date and time in sheet2 for every match it finds.
Thank you.
hi AlexRoberts. a sample excel file with manually typed results you want to see always helps. see if the attached is what you need.
assuming data of sheet2 in A2:B20, then:
=SUMPRODUCT((Sheet2!$A$2:$A$20<=A2)*(Sheet2!$B$2:$B$20>=A2))
the above counts the number of times the date & time occurs between the ranges. if you just want 1 shown when it occurs, then:
=IF(SUMPRODUCT((Sheet2!$A$2:$A$20<=A2)*(Sheet2!$B$2:$B$20>=A2)),1,"")
and if you could update your profile with the latest MS off ver you have (assuming you haven't), that will help us give you the newer & more efficient formulas. in excel 2007 & above, you can use COUNTIFS:
=COUNTIFS(Sheet2!$A$2:$A$20,"<="&A2,Sheet2!$B$2:$B$20,">="&A2)
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks