+ Reply to Thread
Results 1 to 15 of 15

Creating a graph with start times and end times vs time

  1. #1
    Registered User
    Join Date
    01-20-2011
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    41

    Creating a graph with start times and end times vs time

    Hello guys,

    I'm working on a project for work and I really need help on this part.

    I have a data set with two columns, the start time and end time of a machine.

    I need to make a "time-line" or some-sorts that can clearly show when the machine was on and when the machine was off.

    Like a x-y plot with the x-axis being time between
    12:00 AM - 12:00 PM

    and when ever the machine is in use there will be a block covering from the start time to end time, then it'll be blank until the next time the machine is ran.


    Any suggestion would be helpful. Thanks!

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Creating a graph with start times and end times vs time

    What's on the y axis?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    01-20-2011
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: Creating a graph with start times and end times vs time

    Different Dates. So y-axis would be say monday - friday. x-axis a 12 hour period. Then the graph will clearly show when the machine was on and when it was off for those days. But I'm not sure how to do that.

  4. #4
    Registered User
    Join Date
    01-20-2011
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: Creating a graph with start times and end times vs time

    Any help will be appreciated!

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Creating a graph with start times and end times vs time

    Cheers
    Andy
    www.andypope.info

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Creating a graph with start times and end times vs time

    A Gantt chart would probably be a good solution if you don't mind having the bars for each start/stop cycle on a separate line. If you want them all on the same line, I have figured out a way to do it. Let me know if this helps.

    It requires setting up another set of data to graph from, built with formulas referencing the original data. The strategy is to use a scatter graph with lines. Each period that the machine is running is modeled by using the on and off times as X values, and using 1 as a Y value. Then the following period when the machine is off is modeled by the off time (that is, repeating the same end time that we just used in the preceding from the "on" cycle, to the next on time) with a blank Y value.

    This strategy causes the on and off times for the "machine is running" period to be connected by a line. Because the "machine is off" periods have blank data, there are no connecting lines. This gives you a series of bars across the Y=1 line when the machine is on.

    I have one problem. When I use time as the X value, I cannot get Excel to create a time-series chart. If I do that it just uses date, and not time, and collapses everything to same X value. (Although I am attaching a file in 2003 format, I am using 2007 to create it.) As a workaround I have used minute-of-day as the X-axis, from 1-1440. Minute-of-day is a straightforward calculation from the time.
    Attached Files Attached Files

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Creating a graph with start times and end times vs time

    xy-scatter will not do Date axis like a Line will. And the line date chart will not do time.

    You can add dates to your data in A:B and use the xy-scatter chart. You will need to set the min/max/major scale values to useful numeric values of time.
    So 1 hour major unit will be (1/24) = 0.041666667

  8. #8
    Registered User
    Join Date
    01-20-2011
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: Creating a graph with start times and end times vs time

    I can't seem to get my start time to graph but I can get my duration to graph.

    Please refer to sheet 3
    Attached Files Attached Files

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Creating a graph with start times and end times vs time

    Your attachment uses time+date. The original description mentioned only time, so that's what I developed. You will see that I have had to add a calculation to give minutes elapsed from the first start time. This also solves the issue of spanning from one day into the next, which I did not anticipate.

    I left in the shading so you can see how it corresponds to my original example.

    Edit: Sheet3 was blank....
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-20-2011
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: Creating a graph with start times and end times vs time

    I got everything to work but the axis of the graph. I can't seem to put it into (1/24) the auto min is set at 40,000

  11. #11
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Creating a graph with start times and end times vs time

    Quote Originally Posted by khoadphamm View Post
    I got everything to work but the axis of the graph. I can't seem to put it into (1/24) the auto min is set at 40,000
    Not sure whose methods you are using here. Need to see your workbook.

  12. #12
    Registered User
    Join Date
    01-20-2011
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: Creating a graph with start times and end times vs time

    Thanks so much for your help, I got everything to work fine!!!

  13. #13
    Registered User
    Join Date
    09-19-2012
    Location
    USA, Maryland
    MS-Off Ver
    Excel 2010
    Posts
    7

    Arrow Re: Creating a graph with start times and end times vs time

    Quote Originally Posted by khoadphamm View Post
    Thanks so much for your help, I got everything to work fine!!!
    it seems I am trying to do the samething you are ~ how did you get it to work
    I have dates = Y axis (have not prob w/ this)
    Time = X axis ~ would like to see a 24hr time across the bottom, 15 min increments
    ......I can not get X axis to work, would like it to start @ 0000 hours (12am) and end @ 2400 hours
    can someone help I am using excel 2010

  14. #14
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Creating a graph with start times and end times vs time

    Hello blori, and welcome to the forum.

    Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

  15. #15
    Registered User
    Join Date
    12-31-2019
    Location
    WA, USA
    MS-Off Ver
    2016
    Posts
    1

    Re: Creating a graph with start times and end times vs time

    Helpful. Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1