Pivot, Summarize values by Maximum, 24-hour clock

Hi Guys,

I have a pivot table of times. I want the max time in my columns to compare the times but they go overnight. But it gives me 23:00 hours as my max, but I really want max for the next day(so it would be 7 am). I don't have date in my source data, what can I do to fix this?

Thanks.

Re: Pivot, Summarize values by Maximum, 24-hour clock is impeding me.

Your idea worked, I was able to get max times! Now the chart formation is weird though, if you can advise. I have 3 columns in pivot. business date, max finish time and max expected finish time. How come my bar chart y axis isn't a proper alignment (The bar chart times are increase when they should hover around the same mark)? I want to take expected finish time data and draw has it as a line as a measurement. (For each day expected time is the same, the finish time changes). sigh.

Re: Pivot, Summarize values by Maximum, 24-hour clock is impeding me.

To add: The dates are coming up on the y axis. Can't get the axis to properly format :(. Would not like any dates on there , just maybe a 20:00 - 08:00 or even at 0 to 8 AM.

Re: Pivot, Summarize values by Maximum, 24-hour clock is impeding me.

Quote from Luke M;767487

Can you upload your workbook? Dealing with PTs can be tricky w/o actually seeing what's going on. =/

Hi Luke would this help:

[Blocked Image: http://i.imgur.com/LEkadm9.jpg]

I have a problem with my Y Axis.

• I want to make it a fixed time of 20:00 (previous day) to 08:00 next day. Having trouble formatting this, as I do have a slicer and problems fixing.
  

  
  

• The data looks to be increasing, I do not want this look, I am hoping the solution to above could solve this.
  

  
  

  As you can see normal finish time should be a straight horizontal. and finish time should be hovering around that same mark.
  

Re: Pivot, Summarize values by Maximum, 24-hour clock is impeding me.

Here is the file Luke.

Re: Pivot, Summarize values by Maximum, 24-hour clock

1) the graph looks alot better, the lines and the disbursement.
2) the original file does have a start time! would this be useful?

It does make alot of sense to me, definately on the right track, except for the numbering in the Pivot and the axis , as they do not accurately reflect the true data. Ex: finish time on the 8th, should show 6:30.

Re: Pivot, Summarize values by Maximum, 24-hour clock

Hi Luke,

attached is the updated workbook with start times.

Re: Pivot, Summarize values by Maximum, 24-hour clock

Quote from Luke M;767575

Hmm. Well, it looks like then to calculate time taken we could do:
=D2-$C2
in columns F:G. However, after I did that, I noticed a few...oddities. If you look at row 10, E10 is < C10. This would result in a negative time calculation...is this bad data, or is there something else going on?
Also, there are a few places where start time is blank? What does this mean...how should duration be calculated?

Let me explain in the latest upload.

Start time is the actual starting time, finish is actual finish time, Column E is the Expected finish time(possibly an average of historical times).
So sometimes the starting time may be delayed, and start later the the Expected finish time. They are independent of each other really.

If start time is blank finish time should also be blank. So those occurance may just be an error where the start time wasnt recorded(row 141 and 124)

Re: Pivot, Summarize values by Maximum, 24-hour clock

Hi Luke, have you had a chance to look at this? It looks good so far, I think the Y Axis needs to be accurate in terms of time though.

Re: Pivot, Summarize values by Maximum, 24-hour clock

Hi Luke, I have a start and finish time. and col E is the expected time.

Col F is good but some of the hours are in the 40's, however this is what I think for Col F:

1) We need the Time , instead of the time Difference, which may not be possible by doing start - finish. Might just use finish time for this. For example I need to see if it Louis finished before the expected time or was longer then the expected time. I want to compare the finish time to normal finish time (or expected finish time) , The finish time and expected finish time should be in max format in the pivot in order to get the latest time for Louis because each row for each date are in components or stages of the timing. I know its hard to understand.

So Louis on the 8th,each row of him on the 8th would be a checkpoint and a finish time of that particular checkpoint. I would like the end checkpoint, hence max in PT.

Im pretty confused on how to do this, but you are in the right direction. Your first attempt was really good in that you got the lines even, except the timing on the axis and in column Fand G were not the accurate ones.

Hopefully this explanation can clear things up

Re: Pivot, Summarize values by Maximum, 24-hour clock

See Attached. The picture should be clear.