In this sheet people check in, but in D:D (Timeworked) I would like to see how long a shift has been, but cant seem to wrap my head around it...
https://www.excelforum.com/attachmen...1&d=1513074504
In this sheet people check in, but in D:D (Timeworked) I would like to see how long a shift has been, but cant seem to wrap my head around it...
https://www.excelforum.com/attachmen...1&d=1513074504
Does thsi work?
in D2
=MAX(IF(B$2:B$13=B2,C$2:C$13))-MIN(IF(B$2:B$13=B2,C$2:C$13))
Array formula, use Ctrl-Shift-Enter
and copy down the column
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Hi, thanks.
But no, it ce-calculates the whole column evertime a user fills in a new row (ei. get to work).
So instead of having a lot of entries, I get the same 3 times for everyday they've been in
You only supplied one days worth of data.
Try this (untested)
=MAX(IF((B$2:B1000=B2)*(INT(C$2:C1000)=INT(C2)),C$2:C1000))-MIN(IF((B$2:B1000=B2)*(INT(C$2:C1000)=INT(C2)),C$2:C1000))
I'm sorry for not specifying, thanks.
The problem is it doesnt take in to consideration that the same user comes more then once.
It changes that user all dates (which is the same in this test)
I only want to populate the instance he checks out, based on the time he last checked in
https://www.excelforum.com/attachmen...1&d=1513075957
=if(isodd(countif($a$2:a2,a2)),vlookup([@id],$a3:$c$13,3,)-[@[in/out]],"")
@tim201110
That seems to do the trick, but doesn't take into account that by time, the rows will expand with new inputs
=if(isodd(countif($a$2:a2,a2)),vlookup([@id],$a3:index($c:$c,match(100000,$c:$c)),3,)-[@[in/out]],"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks