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.

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" <JayKay@discussions.microsoft.com> wrote in message
news:93123FED-661D-41B7-A990-4C25C52F3C29@microsoft.com...
>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:
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" <JayKay@discussions.microsoft.com> wrote in message
> news:93123FED-661D-41B7-A990-4C25C52F3C29@microsoft.com...
> >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.
> >
>
>
>

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" <JayKay@discussions.microsoft.com> wrote in message
news:3585BCB0-002A-4F52-A98B-A053A1707188@microsoft.com...
> 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" <JayKay@discussions.microsoft.com> wrote in message
>> news:93123FED-661D-41B7-A990-4C25C52F3C29@microsoft.com...
>> >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.
>> >
>>
>>
>>

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" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message
news:e7d9faHhIHA.5208@TK2MSFTNGP04.phx.gbl...
> 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" <JayKay@discussions.microsoft.com> wrote in message
> news:3585BCB0-002A-4F52-A98B-A053A1707188@microsoft.com...
>> 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" <JayKay@discussions.microsoft.com> wrote in message
>>> news:93123FED-661D-41B7-A990-4C25C52F3C29@microsoft.com...
>>> >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.
>>> >
>>>
>>>
>>>
>
>

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
news:K5CdnaunePrrqkXanZ2dnUVZ8u-dnZ2d@bt.com...
>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" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message
> news:e7d9faHhIHA.5208@TK2MSFTNGP04.phx.gbl...
>> 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" <JayKay@discussions.microsoft.com> wrote in message
>> news:3585BCB0-002A-4F52-A98B-A053A1707188@microsoft.com...
>>> 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" <JayKay@discussions.microsoft.com> wrote in message
>>>> news:93123FED-661D-41B7-A990-4C25C52F3C29@microsoft.com...
>>>> >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.
>>>> >
>>>>
>>>>
>>>>
>>
>>
>
>

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" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message
> news:e7d9faHhIHA.5208@TK2MSFTNGP04.phx.gbl...
> > 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" <JayKay@discussions.microsoft.com> wrote in message
> > news:3585BCB0-002A-4F52-A98B-A053A1707188@microsoft.com...
> >> 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" <JayKay@discussions.microsoft.com> wrote in message
> >>> news:93123FED-661D-41B7-A990-4C25C52F3C29@microsoft.com...
> >>> >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.
> >>> >
> >>>
> >>>
> >>>
> >
> >
>
>
>

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" <JayKay@discussions.microsoft.com> wrote in message
news:010CBC10-725E-4BF7-80C6-84F3EE71F88E@microsoft.com...
> 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" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message
>> news:e7d9faHhIHA.5208@TK2MSFTNGP04.phx.gbl...
>> > 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" <JayKay@discussions.microsoft.com> wrote in message
>> > news:3585BCB0-002A-4F52-A98B-A053A1707188@microsoft.com...
>> >> 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" <JayKay@discussions.microsoft.com> wrote in message
>> >>> news:93123FED-661D-41B7-A990-4C25C52F3C29@microsoft.com...
>> >>> >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.
>> >>> >
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>>

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" <JayKay@discussions.microsoft.com> wrote in message
> news:010CBC10-725E-4BF7-80C6-84F3EE71F88E@microsoft.com...
> > 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" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message
> >> news:e7d9faHhIHA.5208@TK2MSFTNGP04.phx.gbl...
> >> > 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" <JayKay@discussions.microsoft.com> wrote in message
> >> > news:3585BCB0-002A-4F52-A98B-A053A1707188@microsoft.com...
> >> >> 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" <JayKay@discussions.microsoft.com> wrote in message
> >> >>> news:93123FED-661D-41B7-A990-4C25C52F3C29@microsoft.com...
> >> >>> >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.
> >> >>> >
> >> >>>
> >> >>>
> >> >>>
> >> >
> >> >
> >>
> >>
> >>
>
>
>

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" <JayKay@discussions.microsoft.com> wrote in message
news32D526A-3D75-44A5-B8CB-5346D8058619@microsoft.com...
> 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" <JayKay@discussions.microsoft.com> wrote in message
>> news:010CBC10-725E-4BF7-80C6-84F3EE71F88E@microsoft.com...
>> > 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" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message
>> >> news:e7d9faHhIHA.5208@TK2MSFTNGP04.phx.gbl...
>> >> > 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" <JayKay@discussions.microsoft.com> wrote in message
>> >> > news:3585BCB0-002A-4F52-A98B-A053A1707188@microsoft.com...
>> >> >> 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" <JayKay@discussions.microsoft.com> wrote in message
>> >> >>> news:93123FED-661D-41B7-A990-4C25C52F3C29@microsoft.com...
>> >> >>> >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.
>> >> >>> >
>> >> >>>
>> >> >>>
>> >> >>>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>