Date and Time Graphs

**sach0025** · 03-08-2007, 05:00 PM

I am stuck in a weird situation. I need to graph the Average Start Time, Average End Time, SLA of batch jobs for the entire year -including the three Regions. Please see below.

Month Region SLA Avg Start Time Avg End Time
Jan Region1 2:00 AM Jan 30, 10:31 PM Jan 31, 1:10 AM
Jan Region 2 5:00 AM Jan 30, 8:28 AM Jan 30, 11:21 AM
Feb Region 1 2:00AM Feb 7th, 2:28 PM Feb 7th, 3:30 PM
Feb Region 2 5:00AM Feb 7th, 11:30 PM Feb 8th, 12:20 AM

I created a graph with x-axis being Jan-Region1, Jan Region2, etc.. My Y-Axis is where the problem is. If I make my Y-Axis to be Date/Time then the data is too much with so many dates and it doesn't look right.

On the other hand, if I plot only Time on my y-axis then the Average Start Time and Average End time is not plotted correctly -- Average Start Time comes after the Average End time -- for example, in Jan Region 1 ---1:10AM (end time) comes before 10:31Pm (start time)- since dates are not included on the graph it shows End Time before the Start Time (where in reality the end time is on another DATE).

I hope I am making sense here. Can anyone please please help me with this??? Is there another way of doing this?

Thanks soooooooooooo much !!

**oldchippy** · 03-08-2007, 05:12 PM

Hello there,

Rather than the sample you have posted, which isn’t quite clear. Is it possible you provide a zipped sample of your spreadsheet with some dummy data in a few lines. This will be easier for all of us to understand what you are trying to do, then hopefully a solution can be found.

**sach0025** · 03-08-2007, 05:31 PM

Thats a great idea !! Attached is the spreadsheet that I am working with.

The blue highlighted area is graphed in either chart 1 or chart 2. The Average Start Time and Average End times are basically the Averages of the times highlighted in tan color.

If you notice in Chart 2, the Y-axis can't handle that much data. The graph doesn't really tell me anything (its got TOO Many dates on the y-axis). If I change this graph and only graph JANUARY data - the graph looks great!!

If you notice in Chart 1, I only have TIME on y-axis. You may notice, Average End Time on the graph for January Region 1 is smaller than Average Start Time -which is inacurate, because as per the dates, the Average Start Time should be smaller since it is a day earlier.

Does it make sense? Please help me lay this out correctly. Thanks much !

**oldchippy** · 03-08-2007, 05:32 PM

No attachment?

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this to your post.

**sach0025** · 03-08-2007, 05:34 PM

Sorry, the attachment wasn't succesful earlier. Here it is!!

**oldchippy** · 03-08-2007, 06:00 PM

You mention that chart 1 is Times only, but the times are inaccurate due to starting the day before. May be this link could help you, look at TIME INTERVALS.

http://www.cpearson.com/excel/datearith.htm#AddingTimes