Time Calculation

[Anonymous]

Dear Experts,

I need your help in the Time calculation.

Kindly refer the attachment.There are many teams involved one task work flow.

Ex.Team 1 starts the task at 09.04.14  at 9:00 Clock(In process)and their work ends at 12.55(Complete) and so on.What I want to calculate is the hours taken by each team and also overall time taken to complete the task.

Team names highlighted in red should not be considered in any calculation

Kindly help me.

Thanks & Regards

Jeba

[anbu1984]

Tab:

Load [Team Involved],Status,Date(Floor(Time),'M/D/YYYY') as Dt,Time;

LOAD ID,

     [Team Involved],

     Status,

     Time

FROM

[Time Calculation.xlsx]

(ooxml, embedded labels) Where (Not Match([Team Involved],'Team4','Team6')) And Match(Status,'Complete','In Process');

NoConcatenate

Final:

Load [Team Involved],Dt,Interval(Time-Previous(Time),'h:mm') Resident Tab Where Status='Complete' Order by [Team Involved],Time;

Drop table Tab;

[maxgro · Accepted solution]

an idea in attachment

[Anonymous]

Hi Massimo,

Thanks a lot

It worked perfertly.

Regards

Jeba

[Anonymous]

Hi Massimo,

Need your help again.

1.There are few cases where I don't have the "In Process" status,but directly its updated as "Complete".I want to update 0 hours spent on those specific cases.Kindly help me

2.Also I want to ignore some specific hours in the calculation.i.e.We have a case ID which starts on Thursday and completed on Tuesday.I want to exclude the hours from Friday 7PM to Monday morning 8AM.

Request you to help me on these points.

Thanks & Regards

Jeba