Grouping dates by month and charting that data (Excel XP)

[itsjustb]

Howdy folks,

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:

=month(mediandate)&"/"&year(mediandate)

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.

Beej

[sdckapr]

Excel treats them as text because they are text. [=month(mediandate)&"/"&year(mediandate)] converts to text

Why not just work with the median dates. In the the pivot you can group by months and you can plot the pivot table with a date axis. You can format the axis to only display the month and year (format as "mm/yyyy", for examle), but you have to keep it a date if you want to work with it as a date.

Steve

[itsjustb]

Steve,

Thanks for the help. I'm not sure how to group by month--it's been a long time since I worked with PivotTables. The help topic (I pasted it below) says that we'll be asked to enter a Starting date and an Ending date, but we're not getting that dialog box at all when we choose Group from the right-click menu. Are we clicking the wrong spot? How would *you* group by month?

Thanks again for the help!

[Help topic starts here]

Right-click the field with the dates or times, point to Group and Show Details on the shortcut menu, and then click Group.

Enter the first date or time to group in the Starting at box, and enter the last date or time to group in the Ending at box.

In the By box, click one or more time periods for the groups.

To group items by weeks, click Days in the By box, make sure Days is the only time period selected, and then click 7 in the Number of days box. You can then click additional time periods to group by, such as Month, if you want.

[sdckapr]

You won't get the dialog box if any of the items in the field are not dates. Are all the items in the field date values (they can not be text that looks like a date, they must be dates).

Steve

[AndrewO]

If you field is actually formatted as a date, not a text item, the grouping works as described in help.

If you want more precise control - it is sometimes useful to calculate a new column called "period" or "tax year" ... which assigns the dates to whatever standardised period you wish.

For instance

=DATE(YEAR((StartDate+EndDate)/2),MONTH((StartDate+EndDate)/2),1)
formated as custom MMM-YY

would express the midpoint between two dates into the first day of a single month. Because the "day" is forced to 1 - all items in this period are seen as the same - things like searches and SUMIFs will then effectively see them as a group.

Formulae, Pivot tables and reports can then express detail rows, counts or summaries within these periods.

[itsjustb]

I was able to group the dates together using the =DATE function recommended by one of the posters (thank you!). However, when I chart this data, the x-axis is showing the day--and I can't get it to change.

I finally extracted all the sensitive data and have attached the workbook I'm working with (the real version is much more complex, but this gets the idea across). Can someone tell me why it won't let me change the x-axis to display "mm-yyyy"??

Thank you, thank you, thank you.

[AndrewO]

Pivot table formatting is different from all other formatting because Microsoft believes that it knows best and reformats whenever you refresh.
You need to let the table know what to do, not the worksheet.

To format fields - ensure that the pivot table options have autoformating set off, and preserve formatting set on.
After this, right-click on the field name, select format and apply your format. [I think} - I continually struggle with precisely where to poke it too <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

[sdckapr]

Your X-axis is not setup as "time-scale"
Right-click chart - chart options
Axes tab select time-scale under category x-axis

Then dbl-click the x-axis select Number tab, date, chose the format

Steve