get time between two rows, rows are not continuous.

FuNeS13

Board Regular
Joined
Oct 25, 2016
Messages
64
I have a spreadsheet that logs the start and finish of a task... I want to get the total time between the start and finish row, the thing is that the rows are not continuous, column A/B and F would be my triggers I guess as column A/B are the employee ID and Column F is the "Start/Finish" legend... the thing is that I have multiple employees filling this...

So lets say that I start my task as 07:01 AM by the time I finish, on 07:31 AM, n number of employees have also started tasks themselves... I have to get the total time for each task, for each employee... The trick is, the total time should be added to the "start" row...

Does this make any sense?

IDEmployee IDNameAreaWCTypeHourDateTime
51321587FuNeS1314602Start07:0110/07/20190:30:00
51311586Other124602Start07:0210/07/20190:07:00
51311586Other124602Finish07:0710/07/2019
51321587FuNeS1314602Finish07:3110/07/2019
51321587FuNeS1314602Start07:5010/07/2019(this is blank because I haven't finish the task yet)

<tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,132
Office Version
365
Platform
Windows
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">ID</td><td style=";">Employee ID</td><td style=";">Name</td><td style=";">Area</td><td style=";">WC</td><td style=";">Type</td><td style=";">Hour</td><td style=";">Date</td><td style=";">Time</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">5132</td><td style="text-align: right;;">1587</td><td style=";">FuNeS13</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4602</td><td style=";">Start</td><td style="text-align: right;;">07:01</td><td style="text-align: right;;">10/07/2019</td><td style="text-align: right;;">00:30:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">5131</td><td style="text-align: right;;">1586</td><td style=";">Other1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4602</td><td style=";">Start</td><td style="text-align: right;;">07:02</td><td style="text-align: right;;">10/07/2019</td><td style="text-align: right;;">00:05:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">5131</td><td style="text-align: right;;">1586</td><td style=";">Other1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4602</td><td style=";">Finish</td><td style="text-align: right;;">07:07</td><td style="text-align: right;;">10/07/2019</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">5132</td><td style="text-align: right;;">1587</td><td style=";">FuNeS13</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4602</td><td style=";">Finish</td><td style="text-align: right;;">07:31</td><td style="text-align: right;;">10/07/2019</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">5132</td><td style="text-align: right;;">1587</td><td style=";">FuNeS13</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4602</td><td style=";">Start</td><td style="text-align: right;;">07:50</td><td style="text-align: right;;">10/07/2019</td><td style=";"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Summary</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=IF(<font color="Blue">F2="start",IFERROR(<font color="Red">INDEX(<font color="Green">$G2:$G$6,MATCH(<font color="Purple">A2&"|"&B2&"|Finish",INDEX(<font color="Teal">$A2:$A$6&"|"&$B2:$B$6&"|"&$F2:$F$6,0</font>),0</font>)</font>)-G2,""</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,683
Office Version
365
Platform
Windows
Slight variation ..

Excel Workbook
ABCDEFGHI
1IDEmployee IDNameAreaWCTypeHourDateTime
251321587FuNeS1314602Start7:017/10/2019
351311586Other124602Start7:027/10/20190:05:00
451311586Other124602Finish7:077/10/2019
551321587FuNeS1314602Finish7:317/10/2019
651321587FuNeS1314602Start7:507/10/2019
Task Time




BTW, Are tasks always finished on the same day they are started?
 
Last edited:

FuNeS13

Board Regular
Joined
Oct 25, 2016
Messages
64
Both answers are correct!!! Thank you both for your great help!!!


BTW, Are tasks always finished on the same day they are started?
^^^
Yes!
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,132
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,683
Office Version
365
Platform
Windows
Both answers are correct!!! Thank you both for your great help!!!
You're welcome.


In that case, and *if* an employee always finishes one task before starting another, you could also use one of these shorter versions.

Excel Workbook
ABCDEFGHIJ
1IDEmployee IDNameAreaWCTypeHourDateTime
251321587FuNeS1314602Start7:017/10/2019
351311586Other124602Start7:027/10/201900:05:0000:05:00
451311586Other124602Finish7:077/10/2019
551321587FuNeS1314602Finish7:317/10/2019
651321587FuNeS1314602Start7:507/10/2019
Task Time (2)
 

Forum statistics

Threads
1,077,662
Messages
5,335,561
Members
399,024
Latest member
rokcel389

Some videos you may like

This Week's Hot Topics

Top