Calculating time

F

Fahadfx

New Member
Joined
Mar 5, 2013
Messages
11
I have a data which looks like this


step_ID
Action date
Action time
1
9/25/2012
11:22:45
2
9/25/2012
11:48:31
3
9/26/2012
12:27:52
4
9/26/2012
13:13:02

<tbody>
</tbody>

The data represent a work flow of files where the file starts at date 9/25/2012 and time 11:22:45, then the next step happen in date 9/25/2012 and time 11:48:31 and continue like this until the file is closed.

I want to create a new column to calculate the time spent in each step to like this
step_ID
Action date
Action time
Calculated time
2
9/25/2012
11:48:31
(9/26/2012 12:27:52) – (9/25/2012 12:27:52)

<tbody>
</tbody>

So I want to calculate the time spent in step 2 through calculating the difference between time in step 3 and 2.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
First I would create a calculated column to concatenate Action Date and Action Time:
DateTime =[Action date]&" "&[Action time]

Make sure you format it as a date/time.

Then I would do your Calculated Time column with something like this:

=LOOKUPVALUE([DateTime],[step_ID],[step_ID]+1) -[DateTime]

LOOKUPVALUE() will return the DateTime of the next step assuming they are numbered consecutively.
 
Upvote 0
Hi again,

I had a new problem and i was not able to solve it here is the data

[TABLE="align: center"]
<tbody>[TR]
[TD]Step-id
[/TD]
[TD]dateTime
[/TD]
[/TR]
[TR]
[TD]1434601997
[/TD]
[TD]1/15/2013 9:47:07
[/TD]
[/TR]
[TR]
[TD]1434601997
[/TD]
[TD]1/15/2013 9:46:58
[/TD]
[/TR]
[TR]
[TD]1434601997
[/TD]
[TD]1/15/2013 9:46:58
[/TD]
[/TR]
[TR]
[TD]1434601997
[/TD]
[TD]1/15/2013 9:46:58
[/TD]
[/TR]
[TR]
[TD]1434601996
[/TD]
[TD]1/15/2013 9:46:38
[/TD]
[/TR]
[TR]
[TD]1434601996
[/TD]
[TD]1/15/2013 9:46:31
[/TD]
[/TR]
[TR]
[TD]1434601996
[/TD]
[TD]1/15/2013 9:46:31
[/TD]
[/TR]
[TR]
[TD]1434601996
[/TD]
[TD]1/15/2013 9:46:31
[/TD]
[/TR]
[TR]
[TD]1434601995
[/TD]
[TD]1/15/2013 9:45:28
[/TD]
[/TR]
[TR]
[TD]1434601995
[/TD]
[TD]1/15/2013 9:45:29
[/TD]
[/TR]
[TR]
[TD]1434601995
[/TD]
[TD]1/15/2013 9:45:28
[/TD]
[/TR]
</tbody>[/TABLE]


I want to calculate the time difference in each step but the problem I’m facing is that the step_id is not unique as one step_id has several moves with several date and time. The end result I want is to create a column to calculate each step time spent as fallows


[TABLE="align: center"]
<tbody>[TR]
[TD]Step-id
[/TD]
[TD]dateTime
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]1434601997
[/TD]
[TD]1/15/2013 9:47:07
[/TD]
[TD](1/15/2013 9:47:07) – (1/15/2013 9:46:58)
[/TD]
[/TR]
[TR]
[TD]1434601997
[/TD]
[TD]1/15/2013 9:46:58
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1434601997
[/TD]
[TD]1/15/2013 9:46:58
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1434601997
[/TD]
[TD]1/15/2013 9:46:58
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1434601996
[/TD]
[TD]1/15/2013 9:46:38
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1434601996
[/TD]
[TD]1/15/2013 9:46:31
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1434601996
[/TD]
[TD]1/15/2013 9:46:31
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1434601996
[/TD]
[TD]1/15/2013 9:46:31
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1434601995
[/TD]
[TD]1/15/2013 9:45:28
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1434601995
[/TD]
[TD]1/15/2013 9:45:29
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1434601995
[/TD]
[TD]1/15/2013 9:45:28
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


The time calculation should be referenced to step_id based on the dateTime order.

Thanks
 
Upvote 0
This formula shows the latest dateTime for that Step-ID prior to the current dateTime:

<earlier([datetime])
<earlier([datetime])
<earlier([datetime])
Code: 
=CALCULATE(MAX([dateTime]),
             FILTER(ALL(Table1[dateTime]),
                           Table1[dateTime]^EARLIER([dateTime])
                            )
                            )

Note, you need to swap the ^ for < (stupid forum does something strange to less thans)

You can then minus that from the current dateTime to give the difference. My concern is around where no prior value exists - for performance reasons I would be reluctant to evaluate the whole thing twice in an IF() although I have no idea how big your dataset is so maybe that's not an issue.</earlier([datetime])
</earlier([datetime])
</earlier([datetime])
 
Upvote 0
Dear Jacop,

Thanks for your replay, it works but the problem is dataset size is large more than 5 million records so my PC is not processing the calculation it gives me a message not enough memory.

thanks
 
Upvote 0
I'm on 64bit , i have reduced the data to 500,000 record but I got the same result out of memory, although my PC has 16G RAM.

Thanks
 
Upvote 0
Thank you Jacop for your help.

Hi,

I have 9 columns in the data set and I’m trying to find a solution.

I was able to get this data to be able to calculate the time for each transaction

T_number
Date
Activity_id
Transaction_Id
1434124406
5/22/2013 15:16:17
3
67
1434124406
5/22/2013 15:16:19
3
63
1434124405
5/22/2013 14:33:04
92
67
1434124405
5/22/2013 14:33:06
92
63
1434124404
5/22/2013 14:40:59
92
67
1434124404
5/22/2013 14:31:09
92
63
1434124404
5/22/2013 14:31:07
92
67
1434124403
5/22/2013 14:20:04
92
63
1434124403
5/22/2013 14:20:03
92
67
1434124402
5/22/2013 14:20:01
4
67
1434124402
5/22/2013 14:17:48
3
67
1434124402
5/22/2013 14:30:55
17
67
1434124402
5/22/2013 14:34:50
8
67
1434124402
5/22/2013 14:35:11
64
67
1434124402
5/22/2013 14:20:02
4
63
1434124402
5/22/2013 14:17:48
3
63
1434124402
5/22/2013 14:35:53
64
63
1434124402
5/22/2013 14:34:38
17
63
1434124402
5/22/2013 14:35:00
8
63
1434124401
5/22/2013 14:17:36
92
63
1434124401
5/22/2013 14:17:34
92
67

<tbody>
</tbody>


I’m trying to get the time difference between the transaction_id for example for T_number 1434124402 we have two several activity_id (4,3,17,8,64) and each activity Id has two transaction id (67,63) so what I want to calculate is


[TABLE="width: 625"]
<tbody>[TR]
[TD]T_number
[/TD]
[TD]Activity_id
[/TD]
[TD]Difference in time
[/TD]
[/TR]
[TR]
[TD]1434124402
[/TD]
[TD]4
[/TD]
[TD](5/22/2013 14:20:02) – (5/22/2013 14:20:01)
(Transaction_Id=63)-( Transaction_Id=67)
[/TD]
[/TR]
</tbody>[/TABLE]

I hope this explains what I want to achieve.

Thanks
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Help win unpivot super slow query
2
Replies
15
Views
1K
labello27
L
M
  • Question Question
PowerBI - allow user to choose a date which will calculate all other dates
Replies
1
Views
144
Matt Allington
M
R
  • Question Question
Power Query Replace Values with IF statement
Replies
5
Views
381
RattlingCarp3048
R
S
  • Question Question
Matrix Visual in Power BI
Replies
2
Views
196
shansakhi
S
S
  • Question Question
Create a function
Replies
1
Views
83
RoryA
RoryA

Forum statistics

Threads
1,223,952
Messages
6,175,588
Members
452,653
Latest member
craigje92

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top