A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Charts and Charting in Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How to chart start and stop times



 
 
Thread Tools Display Modes
  #1  
Old April 8th 05, 05:29 PM
tresscott
external usenet poster
 
Posts: n/a
Default How to chart start and stop times

I am doing a graph of start and stop times for a process and having some
trouble with the data.

I am using a stacked bar with 3-d visual effect and have days on the y axis
and times on the z axis (i would call it the x, but excel seems to use z)

I have figured out how to get the times at the bottom to be even.
Then I figured out how to get 23:30 to be before 1:30 (I changed the latter
to 25:30)

but now the line ends before the axis label in the amount that the process
starts before midnight.

an example
if my process starts at 23:00 and ends at 5:00 (29:00)
the graph will look like this

4/1/05 | XXXXXXXXXXXXXXXXXX
----------------------------------------
23:00 4:00

any hints other than manipulating the data further than I already have to.

for bonus points, you could help with the 23:00 is earlier than 2:00 problem.

Thanks
Ads
  #2  
Old April 10th 05, 11:04 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default

If you use true date-time values for start and stop, like

April 10, 2001 17:50

instead of just 17:50, you don't have to fudge by changing 1:30 to
25:30. Excel stores dates as whole numbers (days since 1 Jan 1900, give
or take an erroneous leap year), and times as fractions of a day (6 am =
0.25, noon = 0.5, 6 pm = 0.75); date times are a composite of a whole
number and a fractional number. The date and time value above is stored
internally as

36991.7430555556

If you want bars showing start and stop time, you might use the
following structure in the worksheet (assume it's in A1:E4):

Start End Chart Start Chart Duration
4/1/2005 4/1/2005 18:00 4/2/2005 1:30 1/0/1900 18:00 1/0/1900 7:30
4/2/2005 4/2/2005 17:00 4/3/2005 2:00 1/0/1900 17:00 1/0/1900 9:00
4/3/2005 4/3/2005 17:30 4/4/2005 2:30 1/0/1900 17:30 1/0/1900 9:00

Columns B and C have the true date-time, column D has the difference
between the true start time and the date (D2 has the formula =B2-A2) and
column E has the duration (E2 has the formula =C2-B2). Ignore the
1/0/1900 in columns D and E (format the cells as time only, not date and
time), since 1/0/1900 is the date representation of zero.

Make your floating bar chart using columns A, D, and E.

> any hints other than manipulating the data further than I already have
> to. for bonus points, you could help with the 23:00 is earlier than
> 2:00 problem.


The above solves the 23:00 < 2:00 problem, and using real date-time
values to start with is not so much a manipulation as a correction. Too
bad it's not clear how Excel treats date-time values until you've
guessed wrong. But going forward, it's going to be a robust approach.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

tresscott wrote:

> I am doing a graph of start and stop times for a process and having some
> trouble with the data.
>
> I am using a stacked bar with 3-d visual effect and have days on the y axis
> and times on the z axis (i would call it the x, but excel seems to use z)
>
> I have figured out how to get the times at the bottom to be even.
> Then I figured out how to get 23:30 to be before 1:30 (I changed the latter
> to 25:30)
>
> but now the line ends before the axis label in the amount that the process
> starts before midnight.
>
> an example
> if my process starts at 23:00 and ends at 5:00 (29:00)
> the graph will look like this
>
> 4/1/05 | XXXXXXXXXXXXXXXXXX
> ----------------------------------------
> 23:00 4:00
>
> any hints other than manipulating the data further than I already have to.
> for bonus points, you could help with the 23:00 is earlier than 2:00 problem.
>
> Thanks

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
what should i use darryll Excel Worksheet Functions 3 March 6th 05 05:26 AM
How can I get elapsed time between AM start/PM stop khacmac Excel Worksheet Functions 1 February 1st 05 11:17 PM
Auto x range start value, xy charts [email protected] Charts and Charting in Excel 5 January 24th 05 11:49 PM
How do I stop the Reviewing toolbar from showing up in Excel? RickKel Excel Discussion (Misc queries) 2 November 26th 04 05:37 PM


All times are GMT +1. The time now is 10:25 AM.


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