If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
charting time
I see; it helped when I formatted the times as numbers. The formula adds one
to times after midnight so that 1:45 am goes from .729 to 1.729, while times before midnight remain as is, 11:45 pm is .9896. So times after midnight are always greater and plot above the pre-midnight times. Excellent. Thanks for all your time. Regards, J "Jon Peltier" wrote: You get back your original time values, not your original date-time values. The values I calculated range from noon on day zero to noon on day 1, so midnight is at 50% of the range. This allows 12:15 to be plotted above 1:45 by 30 minutes, not below it by 23:30. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jay Kay" wrote in message news Thanks for the formula, but I just get back my actual time. Am I doing something wrong? Reducing my problem to a simpler question, how would I plot 3 times, say 11:45 pm, 12:00 am and 12:15 am., where 11:45 pm would show up below 12:00am 12:15am .X 12:00am .........X 11:45pm.................X ..............day1 day2 day3 I am working only with hours/minutes, I am not charting the dates. (If I did, my baseline of 1:30 am would not be a flat line, it would climb, since the date portion of the number is increasing). Thanks and Regards, Jay "Jon Peltier" wrote: My latest suggestion takes care of this.... - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jay Kay" wrote in message ... Thanks for the suggestions, but I'm still stuck. I'd like the hours to be the y axis and the dates to be the x axis, 2:00 am 1:00 am 12:00 am 11:00 pm 10:00 pm 2/5 2/6 2/7 etc My problem arises because I want time prior to 12:00am to be "negative", in that it plots below 12am Thanks Regards, "David Biddulph" wrote: I don't think that helps, does it, Jon? 11:55 PM would come out as nearly 1.5, whereas 1:30 AM would be just above 0.5. You could get what I think you're looking for by using =MOD(A2+0.5,1), but then you would get the same problem either side of mid-day that you're currently seeing around midnight. -- David Biddulph "Jon Peltier" wrote in message ... Oh right. How about =A2-INT(A2)+0.5, which puts midnight at a value of 0.5? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jay Kay" wrote in message ... Jon: The issue with that is that when I chart 11:55 pm, it needs to be below the baseline of 1:30 am (since it occured earlier than the scheduled start time). Just plotting 11:55 pm would put it above than the baseline. Regards, Jay "Jon Peltier" wrote: I think you want two more columns, one for scheduled time without the date, so you use a formula like =A2-INT(A2), the other for actual time without the date. These are the Y values you want. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jay Kay" wrote in message ... I need to create a chart to represent the time an event is scheduled to occur vs when it actually occurred. sched date/time act date/time diff comment 2/5/08 1:30 AM 2/5/08 1:45 AM 15 late start 2/6/08 1:30 AM 2/6/08 12:00 AM -90 early start 2/7/08 1:30 AM 2/6/08 11:45 PM -105 early start 2/8/08 1:30 AM 2/7/08 11:55 PM -95 early start 2/9/08 1:30 AM 2/9/08 2:00 AM 30 late start The chart should have a static line for series 1 (scheduled) at 1:30 am The line for series 2 should move above and below 1:30, depending on if the event started late (rows 1,5) or early (rows 2-4). The best I have been able to accomplish is a chart w/x axis as dates and y axis as the difference in minutes between scheduled start and actual start. The series 1 line is 0's, series 2 is a positive # (if late start) or negative # (if early start) But that's not what I need. I would like to y axis to display times so that a quick look will show, for example, the event occurred at 12:00 on day 2, not that it occurred at -90 (minutes). Any guidance will be appreciated. Thanks. |
|
Thread Tools | |
Display Modes | |
|
|