Excel - Formula - Period Quarter End

Asked By Sa on 23-Aug-07 08:44 PM
I Need a formula to give me the period quarter end.  The date fields I have
to use in the formula are:

Cell B35:  2007

Cell B54 (references period.year):   007.2007

I would like results to show as:  006 / 2007
The formula needs to take into account cross years, so if data was pulled
for February 2007, the "Period Quarter End" would be 12 / 2006.

Here is the formula I was trying to use.  Obviously, it doesn’t work.

=IF(AND(LEFT(B54,3)>6,LEFT(B54,3)<10),006 / B35,"")

Any ideas???




BarbReinhard replied on 23-Aug-07 10:22 PM
Let's say the date field you are referencing is C17.   (has February 2007)

=TEXT(IF(MONTH(C17)<6,12,6),"000") & " / " &
IF(MONTH(C17)<6,YEAR(C17)-1,YEAR(C17))

HTH,
Barb Reinhardt
Sa replied on 24-Aug-07 12:04 AM
Barb, The formula didn't work.  Keep in mind that the period/date field is
not formatted as a month and year.  It is a period (007) and year (2007) and
is in the format 007.2007.  The report is run throughout the year, so the
period will change monthly and I need to go back to previous quarter.
Quarter period/years in 2007 would be:  012/2007, 003/2007, 006/2007,
009/2007.

Thanks.
Sa replied on 24-Aug-07 08:22 AM
The headers on the spreadsheet look like this:

Account   Current Period   Previous Qtr  Previous Period   Current Period
Previous Yr
2134545            007                                    006
006 / 2007

As mentioned previous, the data I have to pull from is:

Period:  007.2007
Year:  2007

I have all the formulas worked out except the previous quarter end (12 /
2007, 003 / 2007, 006 / 2007,  009/2007).  In the example above, the previous
quarter end date would 006 / 2007.
Sa replied on 26-Aug-07 10:34 PM
I received a formula that works.

Thanks for the help.

=IF(ROUNDUP(A1/3,0)=1,"012 /