Edit the formula to make index&match include 3 criteria

Hello Everybody
Explaination:"The formula in ""F ""column should calculate the working hours (last out-first in) based on 2 critera:
1- the same HR ""Employee code""
2- the same date

Please Login or Register  to view this content.

The demand:I want the index formula to take first in.I tried to apply this in column "G" but I got error
Reason:I want to apply the third criteria because the old formula with 2 criteria gets incorrect results

Please Login or Register  to view this content.

please find the attachmnt
Waiting your support

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

Unless there are instances where the two cells in question are not the same HR number and the Out time always follows the In time then you could simplify the F19 formula to

Formula:

Please Login or Register  to view this content.

If you do want to build in a check for the same HR then just preface the formula with an

Formula:

Please Login or Register  to view this content.

Originally Posted by leprince2007

See 1st post I clarified my demand:
I want to correct the formula in "G" column

Well put the formula I gave you in G19 instead of F19!

Originally Posted by leprince2007

Thank you for your answer, but your formula doesn`t give correct answers:

I think you'll find it does.
You are ignoring the seconds contained in the column D times.

For instance D21 is 15:52:54 and D22 is 23:43:11. That's a difference of 7 hours and 17 seconds. If you format G22 to show hh:mm:ss you'll see it correctly shows 7:50:17. Maybe you need to incorporate a rounding - or maybe not. It all depends what you want to SEE.

Originally Posted by leprince2007

Thank you for your answer, but your formula doesn`t give correct answers:

I think you'll find it does.
You are ignoring the seconds contained in the column D times.

For instance D21 is 15:52:54 and D22 is 23:43:11. That's a difference of 7 hours and 17 seconds. If you format G22 to show hh:mm:ss you'll see it correctly shows 7:50:17. Maybe you need to incorporate a rounding - or maybe not. It all depends what you want to SEE.

Originally Posted by leprince2007

Thank you for your answer, but your formula doesn`t give correct answers:

I think you'll find it does.
You are ignoring the seconds contained in the column D times.

For instance D21 is 15:52:54 and D22 is 23:43:11. That's a difference of 7 hours and 17 seconds. If you format G22 to show hh:mm:ss you'll see it correctly shows 7:50:17. Maybe you need to incorporate a rounding - or maybe not. It all depends what you want to SEE.

Originally Posted by Richard Buttrey

I think you'll find it does.
You are ignoring the seconds contained in the column D times.

For instance D21 is 15:52:54 and D22 is 23:43:11. That's a difference of 7 hours and 17 seconds. If you format G22 to show hh:mm:ss you'll see it correctly shows 7:50:17. Maybe you need to incorporate a rounding - or maybe not. It all depends what you want to SEE.

First :No, I want the formula to give the exact hours,minutes and seconds
Second:You ignored that my formula should calculate this (last out-first in)
Download the attachment in post 11# to see what I`am talking about (as I changed the data)
please your support

Originally Posted by leprince2007

Thank you for your answer, but your formula doesn`t give correct answers:

I think you'll find it does.
You are ignoring the seconds contained in the column D times.

For instance D21 is 15:52:54 and D22 is 23:43:11. That's a difference of 7 hours and 17 seconds. If you format G22 to show hh:mm:ss you'll see it correctly shows 7:50:17. Maybe you need to incorporate a rounding - or maybe not. It all depends what you want to SEE.

Originally Posted by leprince2007

First :No, I want the formula to give the exact hours,minutes and seconds
Second:You ignored that my formula should calculate this (last out-first in)
Download the attachment in post 11# to see what I`am talking about (as I changed the data)
please your support

Re 1. Then format G22 as h:mm:ss

Re 2. You ignored the caveat in my original where I mentioned the In/Out pairs. Don't be surprised if you change your data and show us a different set if the formula doesn't continue to work. It's important when you give an example that it accurately reflects all your permutations of data otherwise we all waste our time.

One approach is an array formula in say G22
..sorry, have to attach an image, Sucuri isn't allowing me to enter all the formula

formula.JPG

Originally Posted by leprince2007

I`am sorry for that.

In brief I want to add this criteria:

Please Login or Register  to view this content.

to this formula

Please Login or Register  to view this content.

In order to make the formula bring first "in"
please help me

Have you actually tried the formula I gave you in #17 which I believe gives you the results you expect?

Thank you for your support,But i want the result in last "out" only,not in every "out".
As by doing this, The working hours of every day is doubled and this is wrong.
Please your support

Hi

I can now that Ali has mentioned the Sucuri limitation on the chevrons

Formula:

Please Login or Register  to view this content.

Hi,

Slight variation. Maybe

Formula:

Please Login or Register  to view this content.

Originally Posted by JohnTopley

I suggest you add a "Helper" column (say F)

Good point John. I was coming to the same conclusion myself