Suggestion for Calculating data 2

[ptrifile]

I have a spreadsheet that holds 4 fields. date, time, action and ID#. I will do my best to try and explain my need. I am willing to do this in excel with VBA or in access as well, just not sure the best approach.

I am trying to get the total time elapsed for each ID# entry. Each ID# entry may have multiple actions as well as duplicate actions. I know when an entry starts as the action will contain the word "start" and I know when it ends as the action will have the word "end" in it. There may be actions in between those but i am not concerned about those as I am only looking for total time elapsed. Each action is in its own row. So I simply need to calculate the time it took from started to end for each ID#.

Can someone suggest either excel or Access and give some suggestions on how to accomplish this?

Thank you,

Paul

 

[Andrzejek]

Some sample data and expected results would be nice.
For example:

[pre]
4 fields. date, time, action and ID#.
1/1/2019 7:00AM Class Start 1234
1/1/2019 11:00AM Class End 1234
1/1/2019 12:00AM Lunch Start 1234
1/1/2019 1:00PM Lunch End 1234
1/1/2019 2:00PM Class Start 1234
1/1/2019 3:00PM Class End 1234
[/pre]
And the outcome would be....:

Or better:
[pre]
Start End Action ID Duration
1/1/2019 7:00AM 1/1/2019 11:00AM Class 1234 4 hours
1/1/2019 12:00AM 1/1/2019 1:00PM Lunch 1234 1 hour
1/1/2019 2:00PM 1/1/2019 3:00PM Class 1234 1 hour
[/pre]

---- Andy

There is a great need for a sarcasm font.

 

[ptrifile]

Hi Andy, thanks for the reply, I will show an example of current data and desired outcome below, I now have 3 fields and not 4:

(3 fields)Date/Time field, Action, ID#
8/6/2019 12:00:00 Start 123456
8/6/2019 12:05:00 End 123456
8/6/2019 03:00:00 Start 654321
8/6/2019 03:10:00 other 654321
8/6/2019 03:20:00 End 654321
8/6/2019 11:59:00 Start 456789
8/7/2019 00:05:00 Start 456789

Desired outcome of the above would be:

ID# Duration
123456 0:05:00
654321 0:20:00
456789 0:06:00

This is what I need to accomplish, I assume that access would be the best approach with an SQL query but looking forward to your suggestion(s).

Thanks again, Andy!

Paul

 

[dhookom]

I would place the data in Access and create a query with SQL like:

SELECT ID, Format(Max([DT])-Min([DT]),"Short Time") AS Duration
FROM ptrifile
WHERE Action In ("Start","End")
GROUP BY ID;

[pre][highlight #FCE94F]ID Duration[/highlight]
123456 00:05
456789 00:06
654321 00:20[/pre]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

this can be done right in the Excel Workbook in a new sheet, using MS Query, via Data > Get External Data > From Other Sources > From Microsoft Query... and drill down to your workbook.


The SQL MUST include a Start AND End for each ID. Therefore, a Sub-Query must be performed to assure this condition...

SELECT d.[ID#]
, Format(MAX(d.[DT])-Min(d.[DT]), 'hh:mm') AS [Duration]
FROM 
(Select [ID#], MIN([Date/Time field]) As DT
From [DATA$]
Where [Action]='Start'
Group By [ID#]
UNION ALL
Select [ID#], MAX([Date/Time field]) 
From [DATA$]
Where [Action]='End'
Group By [ID#]
) d

GROUP BY d.[ID#]
Having Format(MAX(d.[DT])-Min(d.[DT]), 'hh:mm') > '00:00';

My result
[pre]
ID# Duration
123456 00:05
654321 00:20

[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

Or in Excel with Formula:
(if you get rid of the record:
8/6/2019 03:10:00 [red]other[/red] 654321)

[pre]
A B C D
Date_Time Action ID# Duration
8/6/2019 12:00 Start 123456
8/6/2019 12:05 End 123456 [blue]5[/blue]
8/6/2019 3:00 Start 654321
8/6/2019 3:20 End 654321 20
8/6/2019 23:59 Start 456789
8/7/2019 0:05 End 456789 6
[/pre]
And the formula in cell D3: [blue][tt] =IF(C3=C2, ROUND((A3-A2)*24*60, 0), "") [/tt][/blue]
Assuming column A if formatted as DateTime mm/dd/yyyy hh:mm


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

...but 456789 has no "End"!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

Ooops!
Paul, fix the data so every ID# has a Start and End record.
Otherwise my D3 Formula would have to be adjusted to check if [tt]B2 = "Start" AND B3 = "End"[/tt]

But then, how come [red]this record[/red]? (Paul?)[pre] ID# Duration
123456 0:05:00
654321 0:20:00[red]
456789 0:06:00[/red][/pre]


---- Andy

There is a great need for a sarcasm font.

 

[ptrifile]

Thanks to the 3 of you! I have not tried the excel solution but Duane, the SQL query in Access works just as desired! Thank you so much for you help!

My example had an error as the last entry for 456789 should have said "End". Every entry has a start and end.

I will give the Excel solution a try as well!

Thanks again!

Paul

 

[ptrifile]

I think I should keep this in this thread. With the help of Duane above, I have this currently working in access but now have come across instances where there is a "start" but no "end". I would like to modify the query below to only calculate the time between records that have a start and end time. Any help is appreciated!

SELECT tblEvolve_Triggers.IDnumber, Format(Max([wDate])-Min([wDate]),"hh:nn:ss") AS Duration, tblEvolve_Triggers.User
FROM tblEvolve_Triggers
WHERE (((tblEvolve_Triggers.Action) In ("Evolvenewstart","Evolve - End")))
GROUP BY tblEvolve_Triggers.IDnumber, tblEvolve_Triggers.User;

Thank you,

Paul

 

[dhookom]

Can you define which have both a start and end time? Do you want to just filter out start dates that are the final records for a particular ID?



Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[combo]

The calculation in Get&Transform environment (Power Query, built in excel since 2016):

- fix (?) input, change "Start" to "End" in last row, otherwise no output for this ID,

- change headers (DateTime, Action, ID), convert range to table (and rename it to tInput),

- first Get&Transform query, only connection, created purely by recorder, advanced editor view:
[pre]let
Source = Excel.CurrentWorkbook(){[Name="tInput"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"Action", type text}, {"ID", Int64.Type}})
in
#"Changed Type"[/pre]

- final second query, output to worksheet, created purely by recorder, some action steps and query name renamed, advanced editor view:
[pre]let
Source = tInput,
FilterForStart = Table.SelectRows(Source, each ([Action] = "Start")),
JoinSourceTable = Table.NestedJoin(FilterForStart, {"ID"}, tInput, {"ID"}, "tInput", JoinKind.LeftOuter),
#"ExpandActionAnd Time" = Table.ExpandTableColumn(JoinSourceTable, "tInput", {"DateTime", "Action"}, {"tInput.DateTime", "tInput.Action"}),
FilterJoinedEnd = Table.SelectRows(#"ExpandActionAnd Time", each ([tInput.Action] = "End")),
AddCalcDurationColumn = Table.AddColumn(FilterJoinedEnd, "Duration", each [tInput.DateTime]-[DateTime]),
RemoveHelperColumns = Table.RemoveColumns(AddCalcDurationColumn,{"DateTime", "Action", "tInput.DateTime", "tInput.Action"})
in
RemoveHelperColumns[/pre]

- output, after formatting Duration as time:
[pre]ID Duration
123456 00:05:00
654321 00:20:00
456789 12:06:00[/pre]

EDIT:
To clear:
- this solution refers to excel 2016 and higher, AFAIK in 2013 Power Query can be installed as an add-in from MS,
- in the second query [tt]Source = tInput[/tt] refers to the first query named [tt]tInput[/tt], created after refering to excel table with this name, I haven't changed it. Power Query refers directly in this way to its own queries (in opposite to excel tables for instance, as in the first query).

combo

 

[ptrifile]

Hi Duane, thanks for the reply and help! What I am trying to accomplish is only calculate the time that has a start (Evolvenewstart) and also an end(Evolve - End) time. The "Evolvenewstart" is always the start of a process and the "Evolve - End" is always the end of a process.

What is happening in my current query is the following. Lets say that I have 4 Evolvenewstart entries for an ID number but no "Evolve - end" time, it is calculating the first "eveolvenewstart" time with the last "Evolvenewstart" time when i would just like to ignore the ID numbers that do not have an "Evolve - End time". So the time calculation should only happen if there is both an "evolvenewstart" and an "evolve - end" time for an ID number.

I hope this cleared this up. Please let me know if you have any other questions. I truly appreciate the help here!

Thank you,

Paul

 

[dhookom]

Most people who have attempted to help you have provided nice formatted sample data using TGML. Look at Andy's posts for a good example of the time he takes to provide data in a readable format.

Can you do the same by providing some sample data as well as the expected results? This should be mandatory for everyone with query question

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ptrifile]

Hi Duane, thanks again, please see below:

The Data looks like this:

[pre]Date_Time Action ID# UserName
8/6/2019 12:00 Evolvenewstart 123456 User1
8/6/2019 12:05 Evolve - End 123456 User1
8/6/2019 1:00 Evolvenewstart 789123 User2
8/6/2019 1:03 Evolvenewstart 789123 User2
8/6/2019 1:10 Evolvenewstart 789123 User2
8/6/2019 1:20 Evolvenewstart 789123 User2
8/6/2019 3:00 Evolvenewstart 654321 User3
8/6/2019 3:20 Evolve - End 654321 User3
8/6/2019 23:59 Evolvenewstart 456789 User3
8/7/2019 0:02 Evolvenewstart 456789 User3
8/7/2019 0:05 Evolve - End 456789 User3[/pre]

Desired results of the query would be the following:

[pre] ID# User Duration
123456 User1 5
654321 User3 20
456789 User3 6
[/pre]

User2 is not represented in the result of the query because while they had an "Evolvenewstart" time, that ID# did not have an "Evolve - End" time.

I hope this helps clarify what I am trying to accomplish.

Thanks again for all of the help!

Paul

 

[Andrzejek]

[pre]
Date_Time Action ID# UserName[blue]
8/6/2019 12:00 Evolvenewstart 123456 User1
8/6/2019 12:05 Evolve - End 123456 User1[/blue]
8/6/2019 1:00 Evolvenewstart 789123 User2
8/6/2019 1:03 Evolvenewstart 789123 User2
8/6/2019 1:10 Evolvenewstart 789123 User2
8/6/2019 1:20 Evolvenewstart 789123 User2[green]
8/6/2019 3:00 Evolvenewstart 654321 User3
8/6/2019 3:20 Evolve - End 654321 User3[/green]
8/6/2019 23:59 Evolvenewstart 456789 User3[red]
8/7/2019 [highlight #FCE94F]0:02[/highlight] Evolvenewstart 456789 User3
8/7/2019 [highlight #FCE94F]0:05[/highlight] Evolve - End 456789 User3[/red]
[/pre]

[pre]
ID# User Duration[blue]
123456 User1 5[/blue][green]
654321 User3 20[/green][red]
456789 User3 [highlight #FCE94F]6[/highlight][/red] <- should this be 3 ?[/pre]



---- Andy

There is a great need for a sarcasm font.

 

[ptrifile]

Hi Andy, thanks for the reply. The result for ID# 456789 should be 6 as I need to use the first or oldest "Evolvenewstart" time not the latest one. I know the nomenclature of the field would seem the other way but i need the first one. I put that in there as an example because my data can have multiple "starts" for an ID#.

Thank you!

Paul

 

[dhookom]

Great question Andy...

We need to know the specific rules to handle more than one start or end next to each other chronologically.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ptrifile]

Andy/Duane, when multiple starts and/or ends happen, I require the First or earliest entry for the start time(Evolvenewstart) and the last time for the end time (Evolve - End).

I appreciate the time and help you guys are giving me!

Thank you,

Paul

 

[Andrzejek]

So the rule is:

[pre]
Date_Time Action ID# UserName[blue]
8/6/2019 12:00 Evolvenewstart 123456 User1
8/6/2019 12:05 Evolve - End 123456 User1[/blue]
8/6/2019 1:00 Evolvenewstart 789123 User2
8/6/2019 1:03 Evolvenewstart 789123 User2
8/6/2019 1:10 Evolvenewstart 789123 User2
8/6/2019 1:20 Evolvenewstart 789123 User2[green]
8/6/2019 3:00 Evolvenewstart 654321 User3
8/6/2019 3:20 Evolve - End 654321 User3[/green] [red]
8/6/2019 23:59 Evolvenewstart 456789 User3[/red]
8/7/2019 0:02 Evolvenewstart 456789 User3[red]
8/7/2019 0:05 Evolve - End 456789 User3[/red]
[/pre]
Outcome:

[pre]
ID# User Duration[blue]
123456 User1 5[/blue][green]
654321 User3 20[/green][red]
456789 User3 6[/red] [/pre]



---- Andy

There is a great need for a sarcasm font.