Skip
# Excel Wizardry Needed

(adsbygoogle = window.adsbygoogle || []).push({});

(adsbygoogle = window.adsbygoogle || []).push({});

Post

# Excel Wizardry Needed

May 9, 2011 12:01 PM Subscribe

Excel question: How can I tally by quarter when all I have are dates? Bonus challenge details inside.

I've got a set of sales clients and information about the purchases they have made over the past year in Excel. Right now all I've got is a series of dates for everyone (dates of purchases). Each date of purchase is its own cell. I'd like to look at what's happening for each client by calendar quarter, specifically how many purchases they are making each quarter. I'd also like to group them by region and look at average number of purchases per region per quarter.

Here's the bonus challenge: The quarters aren't *quite* the same for everyone. They're based on the date of first purchase. So it's their own personal quarter, so to speak. On the plus side, the beginning of the "personal quarter" is limited to dates within a three-month promotional period we held. I'm trying to think of an easy way to do this in Excel, but I am stumped. Even if you have suggestions for how to do this without addressing the bonus challenge, that would be helpful. Thanks!

I've got a set of sales clients and information about the purchases they have made over the past year in Excel. Right now all I've got is a series of dates for everyone (dates of purchases). Each date of purchase is its own cell. I'd like to look at what's happening for each client by calendar quarter, specifically how many purchases they are making each quarter. I'd also like to group them by region and look at average number of purchases per region per quarter.

Here's the bonus challenge: The quarters aren't *quite* the same for everyone. They're based on the date of first purchase. So it's their own personal quarter, so to speak. On the plus side, the beginning of the "personal quarter" is limited to dates within a three-month promotional period we held. I'm trying to think of an easy way to do this in Excel, but I am stumped. Even if you have suggestions for how to do this without addressing the bonus challenge, that would be helpful. Thanks!

For each client, determine the first day of each quarter and add 4 columns to your file called Q1, Q2, Q3 and Q4. Let's say these are columns M-P. Column M will be either the day of purchase or the day after, so assuming day of purchase is in column L, M2 should contain the formula =L2 or =L2+1.

Now, how are you measuring these floating quarters, by days or months? Assuming you are using 91 days, column N2 should now be =M2+91; O2 will be =N2+91 and P2 will be =O2+91. If you are using three months, you will need to extract the month from the date =MONTH(D2) will give you the month of the date in D2. Then add 3 and grab the day and year from the original date (=DAY() and =YEAR() ) and fix the ones that roll over into the next year.

Now you need to determine which quarter each subsequent purchase falls into. A nested IF formula will get you there: =IF(A2<N2,"Q1",IF(A2<O2,"Q2",IF(A2<P2,"Q3","Q4")))) assuming A2 is the subsequent purchase date and N2 is the start of the second quarter (and O2 the third and P2 the fourth) and all of your dates are within 365 days from the purchase date.

posted by soelo at 12:30 PM on May 9, 2011

Now, how are you measuring these floating quarters, by days or months? Assuming you are using 91 days, column N2 should now be =M2+91; O2 will be =N2+91 and P2 will be =O2+91. If you are using three months, you will need to extract the month from the date =MONTH(D2) will give you the month of the date in D2. Then add 3 and grab the day and year from the original date (=DAY() and =YEAR() ) and fix the ones that roll over into the next year.

Now you need to determine which quarter each subsequent purchase falls into. A nested IF formula will get you there: =IF(A2<N2,"Q1",IF(A2<O2,"Q2",IF(A2<P2,"Q3","Q4")))) assuming A2 is the subsequent purchase date and N2 is the start of the second quarter (and O2 the third and P2 the fourth) and all of your dates are within 365 days from the purchase date.

posted by soelo at 12:30 PM on May 9, 2011

...and re-reading, I think I get it a little better (maybe). If the following data is in column A, starting with row 1:

client X

1/1/2000

1/2/2000

1/3/2000

1/4/2000

1/5/2000

1/6/2000

4/2/2000

6/1/2000

Then the following will count all dates within the first one (1/1/2000) and three months later:

=COUNTIFS($A$2:$A$9, ">=" & $A2, $A$2:$A$9, "<" & DATE(YEAR(A2), MONTH(A2)+3, DAY(A2)))

posted by inigo2 at 12:31 PM on May 9, 2011

client X

1/1/2000

1/2/2000

1/3/2000

1/4/2000

1/5/2000

1/6/2000

4/2/2000

6/1/2000

Then the following will count all dates within the first one (1/1/2000) and three months later:

=COUNTIFS($A$2:$A$9, ">=" & $A2, $A$2:$A$9, "<" & DATE(YEAR(A2), MONTH(A2)+3, DAY(A2)))

posted by inigo2 at 12:31 PM on May 9, 2011

PivotTables can group by quarter for you. They could also solve the region problem in the same table. They have a bit of a learning curve, but once you get the hang of them, they make organizing information very easy. You don't say what version of Excel you're using, but here's a place to start for Excel 2003, and another for Excel 2007.

posted by jocelmeow at 1:55 PM on May 9, 2011

posted by jocelmeow at 1:55 PM on May 9, 2011

Oh, jocelmeow beat me to it... I will second PivotTables, which are a feature well worth learning. Once you have the basic skills down, it makes this sort of thing very simple.

posted by OneMonkeysUncle at 4:44 PM on May 9, 2011

posted by OneMonkeysUncle at 4:44 PM on May 9, 2011

This thread is closed to new comments.

One of the things that may help for working within the 3 month period -- the following formula will calculate a date three months after the date in cell A1:

=DATE(YEAR(A1), MONTH(A1)+3, DAY(A1))

posted by inigo2 at 12:27 PM on May 9, 2011