charting time

[Jay Kay]

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.

 

[Jon Peltier]

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

 

[Jay Kay]

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]

Oh right. How about =A2-INT(A2)+0.5, which puts midnight at a value of 0.5?

- Jon

 

[David Biddulph]

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.

 

[Jon Peltier]

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

 

[Jay Kay]

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,

 

[Jon Peltier]

My latest suggestion takes care of this....

- Jon

 

[Jay Kay]

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]

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

 

[Jay Kay]

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