Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting time
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. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting time
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. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting time
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. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting time
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. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting time
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. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting time
Actually, I came up with this formula:
=TIMEVALUE("1:30")+1+C5-B5 where C5 holds the actual time and B5 the scheduled time. Given that the scheduled time was 1:30, the midnight problem seemed more of an issue. This formula seemed okay for actual times within 24 hours of scheduled. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... 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. |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting time
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. |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting time
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. |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting time
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. |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting time
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 ... 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. |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time and date charting | Charts and Charting in Excel | |||
Time-Series Charting | Charts and Charting in Excel | |||
Real Time Charting | Charts and Charting in Excel | |||
Charting Time | Charts and Charting in Excel | |||
Charting time? help please... | Charts and Charting in Excel |