Start and Stop times

[conor]

Ok Gurus, Can you help?

I have data from a machine. The data is On and Off times for different codes. See the attached workbook "Chando Example" and sheet "Alarms On and Off Query".

I need to find the duration in time between the On and Off timestamp for each individual Code. Codes can be repeated.

More out of desperation than knowledge, I hacked the following solution:

A query for the On times: "On"
A query for the Off times: "Off"
Merge the two on "code": "OnOffMerge"
Pivot Table to produce the min time difference by code, date, hour and minute: "PivotWithMin of time difference"

Surprisingly this works, the only exception being when the fault code is repeated within the hour and the Pivot picks the minimum of the two time differences; I really should have both. You can see the calculation to give minutes.

However, I just know that this can be done correctly, perhaps vba or with a more accurately designed combo of query, pivot, vba. I dunno, but the above was as far as I could take it after some serious effort on my part.

So please have a look and see if this can be done
Many thanks
Conor
Ireland

 

[vletm]

conor
Is somewhere the original data?
... because
those 'Date Time's are text and
also that data has 'sorted' those.

 

[conor]

Fair enough, I changed those to date time type in the power query. What I have posted is a simplified example to make the problem as clear as I can. I may have sorted but this I believe does not make a difference to the merge?
Anyway let me know if you require something to be done on this.
Thanks
Conor

 

[vletm]

If You use txt-'Date Time' and Sorted ..
then the result will be OFF-ON-OFF or any combination,
especially Time don't have seconds.
Txt-'Date Time' is not so challenge ... just have to more calculations ... but that Sort .. if same time IN-and-OUT then which is first or is that OUT-and-IN case?
Too much simplified examples makes many times more challenges.