I'm trying to create a chart to compare the overlap of dates. Sort of a timeline with a bar chart that starts on the Onset Date and ends at the Offset date for each category.
I have included sample data below. For this data, I would have 5 bar charts going horizontal with dates on the X-axis. The Y-axis can be the Category type.
Been playing around with line and bar charts in excel but they just are not right. Any suggestions would be great!
Category Onset Offset
B1 12/1/2014 3/1/2015
B2 10/1/2014 1/1/2015
B3 5/1/2014 1/1/2015
B4 6/1/2014 8/1/2014
B5 6/1/2014 10/1/2014
Best Answer
I presume that you want something like this:
Set up another region in your workbook that is the same dimensions as your data area (3×6, in your example). It can be on another sheet; for now, let’s say it is in columns
X
-Z
. Assuming that the data you’ve shown us areA1:C6
, it will beX1:Z6
. SetX1
→=A1
. Drag one column to the right to getY1
→=B1
; drag down to cover all the data (i.e., row 6). The “Onset” dates should now be displayed as numbers (e.g., 41974, 41913, 41760, 41791, and 41791).Note that you can do the same thing as the above with “Copy” and “Paste Link”.
Z2
→=C2-B2
and drag down. This, obviously, gives the duration of eachBn
category.Then
X1:Z6
data.Another option:
MIN(B2:C6)
andMAX(B2:C6)
. Format them to display as numbers. Use these as the minimum and maximum value for the X axis. (You’ll have to copy them as literal numeric values, and they won’t automatically update if the data change.)EOMONTH(MIN(B2:C6), -1) + 1
andEOMONTH(MAX(B2:C6), 0)
to get the first day of the first month in your data set, and the last day of the last month.