#1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 8,651
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 7
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
time and date charting changetires Charts and Charting in Excel 0 August 9th 06 04:02 PM
Time-Series Charting asaylor Charts and Charting in Excel 3 July 19th 06 04:12 PM
Real Time Charting lossofdog Charts and Charting in Excel 1 June 1st 06 09:03 AM
Charting Time Annie Charts and Charting in Excel 1 May 27th 06 02:38 AM
Charting time? help please... Gustavo Monteverde Charts and Charting in Excel 1 November 28th 04 05:53 AM


All times are GMT +1. The time now is 02:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"