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. 


Thread Tools  Display Modes 
#1




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 3d 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




If you use true datetime 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 datetime, column D has the difference between the true start time and the date (D2 has the formula =B2A2) and column E has the duration (E2 has the formula =C2B2). 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 datetime values to start with is not so much a manipulation as a correction. Too bad it's not clear how Excel treats datetime 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 3d 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  


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