Need help with time calculations

[Termig8r]

I have a series of data that I need to sort. It is in the current format as below

Account Number Status Time-Date Date Time

The "time-date" was split to show date in one cell and time in another...but all are showing as text in my Pivot Table Data.

Status incudes "Account created" "Opened" "Saved" and "Closed".

What I want to do is to be able to sort by Date, showing a list of each account number worked on that date, and have it calculate the time spent on each account number going from the time either created or opened to the time closed...saved is irrelevant in this situation. Any suggestions on how to accomplish this feat?

Any help would be greatly appreciated...Thanks!!

 

[theBardd]

How is it being split, in Excel formulae or in Power Pivot? Either way, what is the splitting formula, and have you formatted the results?

 

[Termig8r]

How is it being split, in Excel formulae or in Power Pivot? Either way, what is the splitting formula, and have you formatted the results?

I am sorry, what do you mean by "splitting formula"? I have not formatted the results.

 

[theBardd]

You said earlier ... The "time-date" was split to show date in one cell and time in another...but all are showing as text in my Pivot Table Data.... That was what I was referring to.

Can you post your workbook to facilitate this?

 

[Termig8r]

You said earlier ... The "time-date" was split to show date in one cell and time in another...but all are showing as text in my Pivot Table Data.... That was what I was referring to.

Can you post your workbook to facilitate this?

Here you go....

http://www.gatorsandmore.com/test1.xlsx

 

[scottsen]

but all are showing as text in my Pivot Table Data.

In the powerpivot window did you set the type on the column to Date?

Depending on what you need, you may want to do a calculated column vs a measure (calculated field). but for example, something like this may work as a measure:

[TimeStart] := CALCULATE(MIN(Table1[Date-Time]), Table1[Event Name] = "Record Created" || Table1[Event Name] = "Record Opened")
[TimeEnd] := CALCULATE(MAX(Table1[Date-Time]), Table1[Event Name] = "Record Closed")
[TimeDeltaMinutes] := ([TimeEnd] - [TimeStart]) * 24 * 60

 

[DWig]

You can wrap your equations in datevalue and timevalue.
=datevalue(left(G2,10))
=timevalue(right(G2,12))

Those should work.