• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pull out the first timestamp attempt

geo Jul

New Member
Dear experts​
I would greatly appreciate any assistance on this issue​
I am trying to pull out the first timestamp attempt In And Out From Column C​
First Criteria Date​
Second Criteria Name​
Any assistance is greatly appreciated​
Best Regards​
please note I am using it at home Excel 365 but at work, we use Excel 2016​
 

Attachments

  • Chandoo.xlsx
    26 KB · Views: 3

geo Jul

Here one sample 'between' Your please note.
Sir first of all thank you for your time and your suggested solution​
I tried this formula in Cell H5 "In" without adding 3 Hour 3:00:00​
=INDEX($E$4:$E$75, MATCH(SMALL(IF(($B$4:$B$75=$G5) * ($D$4:$D$75=$H$3), $E$4:$E$75, ""), 1), IF(($B$4:$B$75=$G5) * ($D$4:$D$75=$H$3), $E$4:$E$75, ""), 0), COLUMN(A1))​
the result was perfect same as yours but without adding 3 Hours​
I tried to modify the formula in Cell I5 "Out" but I failed​
=INDEX($E$4:$E$75, MATCH(SMALL(IF(($B$4:$B$75=$G5) * (TEXT($E$4:$E$75, "hh:mm")="17:00") * ($D$4:$D$75=$H$3), ROW($E$4:$E$75)-ROW($E$4)+1, ""), 1), ROW($E$4:$E$75)-ROW($E$4)+1, 0), COLUMN(A1))​
any suggestion to correct my formula will be highly appreciated​
 

geo Jul

My IN-columns no use 3hrs.
My OUT-columns use 3hrs ... to have few hours 'break' before OUT.

Have You tested - how Your (TEXT($E$4:$E$75, "hh:mm")="17:00") works alone?
... for me - if I'll calculate times then ... I'll use times.
 

geo Jul

My IN-columns no use 3hrs.
My OUT-columns use 3hrs ... to have few hours 'break' before OUT.

Have You tested - how Your (TEXT($E$4:$E$75, "hh:mm")="17:00") works alone?
... for me - if I'll calculate times then ... I'll use times.
Many thanks for your response

geo Jul

My IN-columns no use 3hrs.
My OUT-columns use 3hrs ... to have few hours 'break' before OUT.

Have You tested - how Your (TEXT($E$4:$E$75, "hh:mm")="17:00") works alone?
... for me - if I'll calculate times then ... I'll use times.
Thank you very much
 
Shame about being lumbered with obsolete versions of Excel at work :(
The 365 beta version of Excel has PIVOTBY which would give

1727775882287.png
Code:
= LET(
    MINPMλ, LAMBDA(times, MIN(FILTER(times, times>0.5))),
    PIVOTBY(Table1[Name], Table1[Date], Table1[Time], HSTACK(MIN,MINPMλ), , 0, ,0)
  )
 
Back
Top