Home 
Search 
Today's Posts 
#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 
#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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
what should i use  Excel Worksheet Functions  
How can I get elapsed time between AM start/PM stop  Excel Worksheet Functions  
Auto x range start value, xy charts  Charts and Charting in Excel  
How do I stop the Reviewing toolbar from showing up in Excel?  Excel Discussion (Misc queries) 