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.


    I would suggest creating a helper column in your raw data, something with a formula like:
    =EndTime + (EndTime < StartTime)


    What that'll do is that if end time is earlier than start time (such as 7 am to 10 pm), then it'll add 1 (day) to the amount. You can then use this helper column as the new end time, and you should be able to properly calculate your times.


    If this doesn't help, can you post an example of what the raw data looks like and how you're calculating things in the PivotTable?

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

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


    Can't see pic...may be because of work firewall. :(
    Can you upload the file of the pic/workbook? I can usually download items directly from Ozgrid no problem.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

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


    Thanks, that helps a lot. The problem now is that the times in col C:D of your data have a date + a time. We really want just the time to plot in our PT. I was thinking that your original file had a start time and an end time...ok, I think I've got an idea. In the raw data, we need to calculate just the time portion. We can use the MOD function to extract just the decimal (aka, the time) from the cells in B and C. Then, update source data for PT, re-arrange some items. Then, I changed the y-axis scaling and major unit, and we're starting to get to something that I think makes sense. Take a look, and let me know if I'm on the right track.

  • 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


    Can you re-load workbook with start time(s) showing? W/o it, I was having to assuming that everything started at 0:00 of the day listed in col A of the data sheet.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

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


    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?

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • 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


    Sorry Wonka, I missed the first reply.
    W/o a start time for finish, I'm not sure what to put in col E. It looks like the value is the result of some calculation, which might have done as you say, an average time + start. But I don't see that average time...which is what we want in col G. So, I just hard coded it for now. Hopefully in your real file you can see where that time is. I added in some error checking to formula in col F. Give it a look and let me know if we're getting closer.

  • 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


    I'm afraid I'm even more confused than ever. :(
    With the same file, can you manually fill in what you would expect to see in Col F & G? As an added bonus, if you can draw/mimic what you think the graph should look like, that would be superb.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."