Posts by wonka1234

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

    Maybe you have a better way of showing it, I was just thinking of each runners times for the date shown in comparison with the expected time. The stages/components were just to shown the progress and how long each checkpoint took, maybe there is better ways of showing this in a presentation form. Your right the duration is not really what im trying to measure, I need the time of the day they finish . ex: usian finished at 6:30 but expected to finish at 6:25 and starting at 10pm

    I prefer the chart where you had the hours 8 pm to noon the next day , that was really good. how did you alter that axis btw?
    As for the stages, maybe you have some good ideas on how to present that.

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

    Still having trouble with the axis, I have it the same as you:

    Min .83
    Max 1.5
    Major unit .0416
    Minor unit Auto.

    where am I going wrong? Is there another setting?

    Also, this one clock goes to 24, which is kind of tough since the times are supposed to go through the night.
    By stacking components I mean, Row 1 of usain bolt is one component so that would be sanctioned off in the bars, and so forth. Or even have the first 3 rows sanctioned off in the bar, is that possible to do?

    Let me know if this is duration you need , check the attached sheet. I also included sample stages Note: not every runner has the same stage. So the stacks bar for usain bolt on the 8th would becomposed of : NY, Phil, Wash Total, LA total, ending at 06:30 am.

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

    Wow looks great, sorry for confusion. How did you change the axis or know what to change the y axis to? That is exactly what I wanted, I just want to know how you got those decimals for fixing the y axis. Very interesting.

    Why couldnt we just use col D and E for the chart?

    Anyway, I know this is wishful thinking, but is there a way to stack some or all the components within each bar for each date?

    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: Specifiying Header, Advance Filter, Copy to new sheet, Stuck.

    Here is the relevant information, the above is to much. Also ill provide more clarity:

    This code extracts unique values from file(s) columns, it searches a header name(i.e first name)defined in the code, Then pastes into my reconciliation sheet in a vertical format(i.e file name,sheet name, first name, last name)

    Let me first state I actually get no error. But my error would be instead the "Last Name" in my reconciliation sheet is just repeated in column D filled down. Column First Name is done perfectly.

    Can anyone help clean up this code? Perhaps Even help me add additional criteria so I can add more headers to extract?

    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:
    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 this bad data, or is there something else going on?
    Also, there are a few places where start time is blank? What does this 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: Time Difference, 24 hour clock

    Okay ill look more into it. I just keep getting value no matter what I change the cell format too.
    And no that post is different, it pertains to a Pivot table and chart formatting.
    This one is a substraction of times.

    Re: Time Difference, 24 hour clock

    Quote from Grimes0332;767534

    Things are not as they seem... Check the actual contents of the Date columns - these are not dates, but strings. You cannot calcculate using strings.

    What does that mean? how do I fix this?

    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: Time Difference, 24 hour clock.

    Hi Rick Grimes,

    It seems to be the formatting of the cell. The dates need to be in number format. The first row date columns change to a # when I change format to general. Only the first row though.

    Re: Time Difference, 24 hour clock.

    Quote from Grimes0332;767511

    Assuming Start Date in A, Start Time in B with the End date and Time in C & D

    =(C1+D1) - (A1+B1)

    And Custom Format the cell using '[h]:mm'

    Works good for E1 but the fill down isn;t working :S.

    =SUM(C1,D1) - SUM(A1,B1) , works but only if the dates are the same. Doesn't work for the T and T+1's.

    Hi all,

    I would like to get time differences.

    Say I have 4 columns: Start day, start time, end day, end time. I would like to get the difference of the start day and time with the end day and time.

    Problem is, times may be 20:00 on T, and 02:00 on T+1 , giving me a difference of 18 hours. When it should be 4 hours.

    I need some help calculating the true time difference.

    Any help would be greatly appreciated.