Forum Discussion
Variable cell reference for subtraction formula
Hello,
If I have the below data, I want to find out the total duration on each employee's last day of work. This would essentially be the end time of the last event, minus the start time of the first event that starts on the same date. The part I can't figure out is how to vary the formula for a different number of events.
So, with this example: ID 123 has 2 events on 5/4, so the formula is C7 - B6.
But ID 456 has 4 events on the last day (5/8), so the formula is C15 - B12
Note that it's based on the same start date -- so for ID 789, the very last events starts before midnight but ends after midnight. So it would be C21 - B18, since both of those events start on 5/4
Thank you!
In D2:
=IF($A2=$A3, "", $C2-MIN(IF(($A$2:$A$18=$A2)*(INT($B$2:$B$18)=INT($B2)), $B$2:$B$18)))
Adjust the ranges $A$2:$A$18 and $B$2:$B$18 for your setup.
Format D2 as a time, then fill down.
3 Replies
This answer is simply to record a different way of working.
First I wrote a Lambda function to return the last day's hours for an individual employee
HoursLastDayλ =LAMBDA(empl, LET( end₁, MAXIFS(End, Employee, empl), day₁, INT(MAXIFS(Start, Employee, empl)), start₀, MINIFS(Start, Employee, empl, Start, ">=" & day₁), duration, end₁ - start₀, duration ) )The tricky bit was returning the final datetime from the 'End' column but using the 'Start' column to determine the last date. The worksheet formula then becomes
= LET( employeeName, UNIQUE(Employee), hours, MAP(employeeName, HoursLastDayλ), HSTACK(employeeName, hours) )which results in
(the data for 456 was not entered)
In D2:
=IF($A2=$A3, "", $C2-MIN(IF(($A$2:$A$18=$A2)*(INT($B$2:$B$18)=INT($B2)), $B$2:$B$18)))
Adjust the ranges $A$2:$A$18 and $B$2:$B$18 for your setup.
Format D2 as a time, then fill down.
- Thank you!
