Hi All,
I have attached a sheet, as you can see column A and F have some dates, Column B time which increase every 15 min and Column G with time increases every 10 min. Column C has values of 1 and 0. I am looking for a fuction to look if dates (Col A and F) are matched and if time in col G are equal or included in time in Col B then the value in Col C be in Col H. I have shown my expectation in red in the attached. Thanks for your help.
Hi hanif,
If you create a helper column for each and Add the Date and Time together it would be an easier problem, but find the attached where an Array formula works without helper columns. To enter an Array formula you need to hold down the Ctrl+Shift and then press the Enter key. We call these CSE formulas. When successful the curly brackets will appear around the formula.
In C2 the answer looks like:
=IF(ISERROR(MATCH(A2+B2,$F$2:$F$26498+$G$2:$G$26498,0)),0,1) (entered with a CSE)
In H2 it is:
=IF(ISERROR(MATCH(F2+G2,$A$2:$A$15931+$B$2:$B$15931,0)),0,1) (entered with a CSE)
CSE Match Date Plus Time.xlsx
I've reduced the size of the attached file so it would fit on this board to only a 1000 rows.
Last edited by MarvinP; 08-03-2016 at 10:44 AM.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Thanks Marvin
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
hanif 
Register To Reply
Bookmarks