For each study we do, we have a starting date (x) and an ending date (y). What we're trying to show is how many studies we have going on by month.
Since most of our studies start in one month and end in another, we're using the MEDIAN function to arrive at a median date. When we chart our data, we don't want to chart individual dates, we want to chart by month. Currently I'm doing this by parsing the dates and building a text entry:
We then use a pivot table to group entries by month and by department, and a pivot chart to show the data.
Unfortunately, when I chart this data, Excel treats these entries as text, so the "months" aren't sorted correctly--i.e. 1/2004 comes before 12/2003.
1) Is there a better way to arrive at the median date?
2) What's the best way to group our data by month?
3) Is the pivot table an extra step we don't need?
Thanks in advance for any help and advice.