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.

 How to chart start and stop times
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## How to chart start and stop times

#1
April 8th 05, 05:29 PM
 tresscott external usenet poster Posts: n/a
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
#2
April 10th 05, 11:04 PM
 Jon Peltier external usenet poster Posts: n/a

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 Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 08:43 PM.