Best way to summarize into month

**bluerog** · 02-22-2011, 05:03 PM

I have worksheets with sales by day in format mm/dd/yy.

I have a need to take 1,000's of these dates and pivot table into monthly sales totals.

If A1 is date, my B1 cell formula to do this is this:

Please Login or Register  to view this content.

. I then format as mmm-yy. This basically turns all dates to first day of the month.

Can you think of a smoother formula that does this?

Thanks much.

**scottylad2** · 02-22-2011, 05:09 PM

Originally Posted by bluerog

I have worksheets with sales by day in format mm/dd/yy.

I have a need to take 1,000's of these dates and pivot table into monthly sales totals.

If A1 is date, my B1 cell formula to do this is this: =(MONTH(A1)&"/01/"&YEAR(A1))+0. I then format as mmm-yy. This basically turns all dates to first day of the month.

Can you think of a smoother formula that does this?

Thanks much.

=EOMONTH(A1,-1)+1 will turn the date to the 1 of whatever month is in A1. do you need to incorporate the year also?

**MarvinP** · 02-22-2011, 05:13 PM

Hi bluerog,

I just built a table for another question. You don't need to do any formulas with the pivot tables. Look at the table on the attached as an example. You simply click the correct month in the pivot date dropdown and the month is filtered correctly.

**bluerog** · 02-22-2011, 05:14 PM

Perfect Scottylady. Thanks much. I knew there was a simplier way.

And for year, I just use:

Please Login or Register  to view this content.

and it does the trick.

For Quarter, I pivot table my Months, the vlookup the month (B2) to find the Q2-2007, Q3-2007 or Q4-2011, etc.... Any of you geniuses have a good way to group dates into quarters?

**bluerog** · 02-22-2011, 05:21 PM

I have used filters in pivot tables. This is a great eg Marvin. (One I never even thought of). As you'll see right below your solution, the only other reason for adding a column with months is to work off that to come up with "Qtr-Year" sales summary.