Need to make a timeline table

[Iane84]

So my boss asked me to make a timeline table and I can't for the life of me seem to make one. He wants data we have from our print production schedule to be displayed in a daily timeline so he can go about scheduling for the right amount of people. Any advice?

[sinspawn56]

Do you have an example of this data that you're provided?

[Iane84]

Here are some examples

Machine 1
Date Newsletter Start Time Stop Time
5/16/2016 Golden Crest 10:44 11:09
West Wind 11:13 11:59
Crosswinds 12:01 12:36
Blue Jay 12:39 13:27
Honeymoon 15:14 15:50
Americana Cove 16:24 9:27


Machine 2
Date Newsletter Start Time Stop Time
5/16/2016 Lake Haven 10:56 12:12
Lake Highlander 12:24 15:22
Serendipity 14:39 16:10
Roberts 16:33 17:13
Regency Heights 17:26 18:56


Machine 1
Date Newsletter Start Time Stop Time
5/17/2016 Briar Creek 1 9:57 11:33
Southwind 11:36 12:33
East Bay Oaks 12:35 15:12
Colonial Village 15:14 15:35
Harbor Lights 15:36 16:05
Lake Seminole 16:07 16:47
Ranchero Village 17:01 10:45


Machine 2
Date Newsletter Start Time Stop Time
5/17/2016Riveria Estates 9:15 10:01
Briar Creek II 10:04 11:11
Twin Lakes 11:14 11:54
Glen Ellen 11:56 12:23
Bay Ranch 12:28 14:42
Crystal Lake 14:45 15:26
Gateway 15:27 16:45
Paradise Island 16:58 11:29

[JeteMc]

I assume that you copied that data from a spreadsheet. It would be helpful if you could upload the spreadsheet itself so that members won't have to place the data on another spreadsheet in order to work with it. There are some issues that I noticed such as 'Americana Cove 16:24 9:27', looks like this runs from 4:24 pm on 5/16 until 9:27 am on 5/17 is that correct? Also could you give an idea about what your boss means by a timeline table? Perhaps have him manually mock up a sample.
To attach a sample workbook, click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Iane84]

Ok i uploaded all the data he wants on the timeline and yeah some of the times run over onto the next day.

[stasinek]

my suggestion - a solution by the addition of Power Query

[Iane84]

Not exactly what im looking for, but I do appreciate it.

[stasinek]

Show how to get the result you want?

[Iane84]

I think what im having a major issue with is getting an adequate Chart out of it. I mean the data is there (obviously) just need to figure out a chart for it

[JeteMc]

This proposed solution makes some changes in the file attached to post #5. Notice that the Machine numbers and dates are filled in for columns A and B. This can pretty easily be accomplished using a modification of the GoTo Special Blank method, where A2:B180 is selected. Another change is that print runs that go into the next day are shown for both days, example American Cove runs from 16:24 until midnight on the 16th and then from 9:00 to 9:27 on the 17th. Midnight (24:00) is added to the times listed in column L. Put the date that you want to review in J1 and column headers "Machine 1" and "Machine 2" in M1 and N1 respectively. After making these changes paste the following formula into M2, drag over to N2 and with both cells still selected double click to fill down:

Please Login or Register  to view this content.

Let me know if you have any questions.