×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Suggestion for Calculating data
2

Suggestion for Calculating data

Suggestion for Calculating data

(OP)
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

RE: Suggestion for Calculating data

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

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
 
And the outcome would be....: ponder

Or better:
      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
 

---- Andy

There is a great need for a sarcasm font.

RE: Suggestion for Calculating data

(OP)
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

RE: Suggestion for Calculating data

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

CODE --> SQL

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

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

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

RE: Suggestion for Calculating data

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...

CODE

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

ID#     Duration
123456	00:05
654321	00:20

 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Suggestion for Calculating data

Or in Excel with Formula:
(if you get rid of the record:

8/6/2019 03:10:00 other 654321)

    A             B      C         D
Date_Time	Action	ID#	Duration
8/6/2019 12:00	Start	123456	
8/6/2019 12:05	End	123456	5
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
 
And the formula in cell D3: =IF(C3=C2, ROUND((A3-A2)*24*60, 0), "")
Assuming column A if formatted as DateTime mm/dd/yyyy hh:mm


---- Andy

There is a great need for a sarcasm font.

RE: Suggestion for Calculating data

...but 456789 has no "End"!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Suggestion for Calculating data

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

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


---- Andy

There is a great need for a sarcasm font.

RE: Suggestion for Calculating data

(OP)
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

RE: Suggestion for Calculating data

(OP)
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!

CODE

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

RE: Suggestion for Calculating data

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

RE: Suggestion for Calculating data

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:
let
    Source = Excel.CurrentWorkbook(){[Name="tInput"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"Action", type text}, {"ID", Int64.Type}})
in
    #"Changed Type" 

- final second query, output to worksheet, created purely by recorder, some action steps and query name renamed, advanced editor view:
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 

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

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 Source = tInput refers to the first query named tInput, 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

RE: Suggestion for Calculating data

(OP)
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

RE: Suggestion for Calculating data

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 glasses

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

RE: Suggestion for Calculating data

(OP)
Hi Duane, thanks again, please see below:

The Data looks like this:

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 


Desired results of the query would be the following:

 ID#        User        Duration
123456      User1       5
654321      User3       20
456789      User3       6
 

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

RE: Suggestion for Calculating data


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 
 

 ID#        User        Duration
123456      User1       5
654321      User3       20
456789      User3       6  <- should this be 3 ? 


---- Andy

There is a great need for a sarcasm font.

RE: Suggestion for Calculating data

(OP)
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

RE: Suggestion for Calculating data

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

RE: Suggestion for Calculating data

(OP)
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

RE: Suggestion for Calculating data

So the rule is:

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 
 
Outcome:

 ID#        User        Duration
123456      User1       5
654321      User3       20
456789      User3       6  


---- Andy

There is a great need for a sarcasm font.

RE: Suggestion for Calculating data

(OP)
Andy, that is absolutely correct and what I am looking for.

Paul

RE: Suggestion for Calculating data

If in access, why not simply in one query select min. time for user with Action filter 'Evolvenewstart', in the second do the same for max and "Evolve - End' Action, join queries for ID# and user and calculate duration (if joining users in necessary - doubled IDs among users)?

Any more compliations in data structure?

As I mentioned, the same can be done with excel queries if initial table is already in excel.

combo

RE: Suggestion for Calculating data

I would try this SQL:

CODE --> SQL

SELECT tblEvolve_Triggers.Date_Time, 
Format([Date_Time]-
  (SELECT TOP 1 Date_Time
   FROM tblEvolve_Triggers T 
   WHERE T.[ID#] = tblEvolve_Triggers.[ID#] AND t.Action = "EvolveNewStart" AND  T.Date_Time < tblEvolve_triggers.Date_time ORDER BY t.Date_Time),
   "Short Time") AS Duration, 
(SELECT TOP 1 Date_Time
 FROM tblEvolve_Triggers T
 WHERE T.[ID#] = tblEvolve_Triggers.[ID#] AND t.Action = "EvolveNewStart" AND  T.Date_Time < tblEvolve_triggers.Date_time ORDER BY t.Date_Time) AS StartTime, 
tblEvolve_Triggers.Action, tblEvolve_Triggers.[ID#], tblEvolve_Triggers.UserName
FROM tblEvolve_Triggers
WHERE (((tblEvolve_Triggers.Action)="Evolve - End")); 

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

RE: Suggestion for Calculating data

(OP)
Combo, thank you for the suggestion, I will give that a try as well.

Duane, thank you as well. When I ran the query you provided I am getting multiple instances of an ID number for the same user. I can only assume that is because there can be multiple "end" times as well? Is there a way to modify this so that it is only showing one ID number per user using only the first "start time" and last "end" time?

I hope the above made sense.

I cannot thank you enough for your help here.

Paul

RE: Suggestion for Calculating data

Try my solution with the sample data you provided. It worked for me. If it doesn’t work with more data then you need to provide a better sampling of data.

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

RE: Suggestion for Calculating data

Quote:

I am getting multiple instances of an ID number for the same user

Please post the data in your table for that ID number.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Suggestion for Calculating data

Quote (ptrifile )

there can be multiple "end" times as well

That's a new one....
Like Duane said: "you need to provide a better sampling of data. "
We cannot help you if we need to guess what data you have.

If you would do this from the beginning:
  • This is what I have
  • This is what I've tried
  • This is what I need as the outcome
This issue would be solved a long time ago.


---- Andy

There is a great need for a sarcasm font.

RE: Suggestion for Calculating data

Well said

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close