Need help in finding any formula to get difference of IN and OUT timing

I am Sorry, I detailed my question to explore what kind of help i need.

Dear Expert Family,

I need help in finding formula, attached sample sheet is for your ease.

Need help to get a formula vlookup the data and get difference between IN and OUT. I also added IF formula but i am not reaching how do i get the difference.

Thanks in advance.

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Regarding your message: your title needs to summarise what you want the formula to do. Please change it to reflect this. Thanks.

EDIT: Title now acceptable - thank you.

Can we add a helper column to the source data tab to calculate this? It will make things MUCH easier.

Yes AliGW, we can add column. And i am sorry for my mistake.

Thanks to help me in correction.

System H3 copied down:

=IF(F3="IN","",LOOKUP((E3-E2)*24,{0,2,4},{"Short Leave","Half Day","Full Day"}))

Summary D3:

=INDEX('System extracted'!$H$3:$H$26,MATCH(1,('System extracted'!$A$3:$A$26=$B3)*('System extracted'!$B$3:$B$26=$A3)*('System extracted'!$D$3:$D$26=D$2)*('System extracted'!$F$3:$F$26="OUT"),0))

You don't need to change anything. It's working fine (see attached).

AliGW on MS365 Insider (Windows) 64 bit

	E	F	G	H
11	10:00	IN
12	15:15	OUT		Full Day
13	8:00	IN
14	9:15	OUT		Short Leave
15	7:22	IN
16	11:15	OUT		Half Day

Sheet: System extracted

AliGW on MS365 Insider (Windows) 64 bit

	A	B	C	D
2	Employee Name	Emp Number	22/01/2022	22/01/2022
3	A	SP-01	P	Full Day
4	B	SP-06	P	Full Day
5	C	SP-07	P	Half Day
6	D	SP-09	P	Full Day

Sheet: Manual format

You may need to enter the second formula with CTRL+SHIFT+ENTER before copying down.

You're welcome.

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.