Row Negate

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

Book1.xlsx

Workflow

Developer

Developer Tools

Accepted answers

Luke_C

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.

New Workflow1.yxmd

KarolinaRoza

Hi @PNC97 ,

Please see attached.

Karolina

Row negate.yxmd

All comments

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"

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703