Forum Discussion
calculate datedif if mutiples conditions within a range are met
Dear Community, hopefully someone can help with the issue I am having.
I am trying to calculate the days between a certain date and today's date if multiples conditions are met.
=IF(AND(D3:D77="<>",E3:E77="",F3:F77="NKD"),DAYS(TODAY(),D3:D77),"")
I want to calculate the days between specific date and todays date only if a cell in column F contains NKD then look in the adjacent cell in column E if its blank and then if D isn't Blank (D is the start date)
is any conditions are false I want a blank in the cell.
I am able to make the formula work if I enter the specific cell containing NKD instead of the range without the D cell (="<>") condition but I need it to automatically find the cell containing NKD within a range
Hopefully this make sense !! 🙂
Thank you in advance for anyone that can help... even after hours searching online and trying I cant figure it out 😕
5 Replies
Small sample file worth many words. Wyn make one assumption, I bit another:
=IFNA(TODAY()-INDEX($D:$D,MATCH(1,INDEX( ($F:$F="NKD")*($E:$E="")*($D:$D>1),0),0)),"")not sure what did you mean exactly.
Wowwwww, this is exactly what I was looking for. I don't understand the formula but its working.
Looks like a foreign language to me, i got so much to learn !!
thank youuu Sergei
SergeiBaklanwaglagla , you are welcome.
The multiplication
($F:$F="NKD")*($E:$E="")*($D:$D>1)check you criteria for each row from 1 to last in the sheet. Since TRUE is equal to 1 and FALSE to 0, it returns 1 only for the row where all three criteria are met. Other words you have sequence like {0,0,0,1,0...}. Inner INDEX which wrap this multiplication return this entire array to next function since we use INDEX(...,0) with zero as second parameter.
Next MATCH(1, ... ,0) finds the exact position (last parameter =0) of number 1 in this array. Actually that's the number of the row for which all criteria met.
Having this position we extract the date in column D with outer INDEX from received row number, and subtract this date from TODAY().
If there is no row for which all criteria are met, above formula returns #N/A error. IFNA checks if such error appears and returns empty string (last parameter), otherwise result of the calculation.
Above is not strong explanation but hope helps to understand the formula.
Hi waglagla
Trying to apply it to a full range in one formula gets tricky and may require an array
Will something like the attached work?
- Thank you for your time Wyn.
Yes its something like the attached work.
The formula Sergei posted worked like a charm.
