SOLVED

Query the date with number input in the excel array

New Contributor

Hello Experts,

I have a trouble with my data, could you please help to support finger out solution about MS formula

Cell: H5 i would like to show the "date In put" (should be: 8/1/2021)

Cell: I5 i would like to show the "date out put" (should be: 10/26/2021)

Row 6: In put 2 time, i would like to show the 1st date (starting date in put)

Row 7: Out put 2 times, i would like to show the last time (complete out put)

Much appreciate with your reply. thanks you

luungockhai_3-1641810246959.png

 

 

 

6 Replies
If you have at least Excel 2019 or later (i.e. Excel 365) then you can use MINIFS() so something like this should give you the starting date on row 6:
=MINIFS($N$3:$Q$3,$N6:$Q6,">0")
Thanks @mtarler I still using MS 2010 it is not workable. BTW thanks for your reply
best response confirmed by luungockhai (New Contributor)
Solution
There is likely an easier solution but try this:
=INDEX(3:3,1,AGGREGATE(15,6,1/(1/(ISNUMBER($N6:$Q6)*COLUMN($N6:$Q6))), 1))
A conditional function will help here. For the input cells use something like:
IF(N5>0,N3,IF(O5>0,O3,IF(P5>0,P3,IF(Q5>0,Q3,""))))
for the output side, use:
IF(S5>0,S3,IF(T5>0,T3,IF(U5>0,U3,IF(V5>0,V3,""))))

@mtarlerhello Mtarler,

i make some report, i got trouble, i can not solve it. could you please do me a favor!

I would like to get the "date" in column J & K. Below is my expectation, i already add comment into attached file. could you please review it to understanding my description. Thanks mtarler.

luungockhai_0-1642158841223.png

luungockhai_2-1642158902652.png

 

@luungockhai ok so it got a bit more complicated... i hope I included all the caveats in the attached