formula to pull various dates within the month to one month in a certian order

[Jadegirl]

Trying to pinpoint the formula for pulling various dates in the month to one date in order of the FY so that I can create a pivot table. I place the (#) in front of the month so that the pivot table will sequence correctly.

For example, my fiscal year starts in July and I have the following:

July 7, 2010 Need to say: (1) July 2010
July 22, 2010 Need to say: (1) July 2010
July 25, 2010 Need to say: (1) July 2010
August 4, 2010 Need to say: (2) August 2010
August 8, 2010 Need to say: (2) August 2010

I have been trying a few formulas, but it isn't quite there yet. any ideas?

Thanks!

 

[Peter_SSs]

I've moved your question to the Excel Questions forum.

Try this. If it doesn't work please advise what Excel version you are using.

Excel Workbook
	A	B
1
2	7/07/10	(1) July 2010
3	22/07/10	(1) July 2010
4	25/07/10	(1) July 2010
5	4/08/10	(2) August 2010
6	8/08/10	(2) August 2010
7	5/09/10	(3) September 2010
8	3/10/10	(4) October 2010
9	31/10/10	(4) October 2010
10	28/11/10	(5) November 2010
11	26/12/10	(6) December 2010
12	23/01/11	(7) January 2011
13	20/02/11	(8) February 2011
14	20/03/11	(9) March 2011
15	17/04/11	(10) April 2011
16	15/05/11	(11) May 2011
17	12/06/11	(12) June 2011
18	10/07/11	(1) July 2011
Dates

 

[Jadegirl]

Awesome - it worked. THANKS! I have been looking up a few videos on the EDATE function. I am not sure what the 6 means (EDATE(A4,6)) in the formula. Would this be the month prior? Thanks a again &much appreciation. I wish the formulas could come so quickly to me!!!!!!

 

[Peter_SSs]

EDATE(A4,6) means the date 6 months after the date in A4.

So for a July date, 6 months later is a January date and grabbing that month gives a 1 which is what you want for July.

For a June date, 6 months later is a December date and grabbing that month gives a 12 which is what you want for June. etc

(To get the date 6 months prior you would use EDATE(A4,-6). This would actually work just as well for you in this particular case.)

 

[Jadegirl]

It seems to be working well -THANKS!