Row Negate

[PNC97]

I had a data with emp id, event date, punch in and Punch out and if employee Id and event date repeats next row then Apply row formula as below Row+1: punch out - Row-1: Punch In

As per image I want to calculations.

So that I can find break in between

I had attached samples of data

[DawnDuong]

Hi @PNC97 

this can be done with a single Multiple row formula.

The formula is almost identical to what you put in your question!

check out the interactive lessons for bite-sized 2-3m lessons for Excel users. Your future self will thank you ^.^
Dawn

[KarolinaRoza]

Hi @PNC97 

Yeap, you need to use Multi-Row formula, but you need to little bit adjust your formula.

Do you want to calculate Punch Time Out - Punch Time In (from previous row, for each employee, right?) 

if [Employee ID]=[Row-1:Employee ID] and [Row-1:Event Date]=[Event Date] 
then [Punch Out Time]-[Row-1:Punch In Time]
else null() 
endif

 in Multi-Formula setup I recommend to chose Create New Filed as Type: Double 

Let me know if you need help with it,

Karolina

[PNC97]

Yeah @KarolinaRoza 

it will be helpful if I had an workflow.

and i tried with the formula but I got total hours instead break hours  

And if Emp ID and event date doesn't have break then it should be "ZERO"

[Luke_C · Accepted solution]

Hi @PNC97 

Here's an example. Very similar to @KarolinaRoza 's suggestion except I used the built-in group by functionality to execute by employee/date.

[KarolinaRoza · Accepted solution]

Hi @PNC97 ,

Please see attached.

Karolina

[PNC97]

@KarolinaRoza  & @Luke_C  

thanks for your help it worked 😊

[KarolinaRoza]

Hi @PNC97 

Now, Looking at @Luke_C  outcome I am not sure which values you would like to achieve.

My solution provides you difference between Time Out - Time In (looks like total time), if you want get break time you need to adjust formula to calculate:  [Punch In Time]-[Row-1:Punch Out Time]  instead [Punch Out Time]-[Row-1:Punch In Time]

It depends what you need 😉

Karolina