If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Formula  Period Quarter End
I Need a formula to give me the period quarter end. The date fields I have
to use in the formula a 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??? 
Ads 
#2




Formula  Period Quarter End
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 "Sam" wrote: > I Need a formula to give me the period quarter end. The date fields I have > to use in the formula a > > 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??? 
#3




Formula  Period Quarter End
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. "Barb Reinhardt" wrote: > 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 > > "Sam" wrote: > > > I Need a formula to give me the period quarter end. The date fields I have > > to use in the formula a > > > > 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??? 
#4




Formula  Period Quarter End
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. "Sam" wrote: > 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. > > "Barb Reinhardt" wrote: > > > 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 > > > > "Sam" wrote: > > > > > I Need a formula to give me the period quarter end. The date fields I have > > > to use in the formula a > > > > > > 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??? 
#5




Formula  Period Quarter End
I received a formula that works.
Thanks for the help. =IF(ROUNDUP(A1/3,0)=1,"012 / "&YEAR(NOW())1,TEXT((ROUNDUP(A1/3,0)1)*3,"000")&" / "&YEAR(NOW())) "Barb Reinhardt" wrote: > 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 > > "Sam" wrote: > > > I Need a formula to give me the period quarter end. The date fields I have > > to use in the formula a > > > > 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??? 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Excel should have a QUARTER formula for DATE calculations  Jeff Stubing  Excel Worksheet Functions  5  October 9th 06 04:27 AM 
Formula for Calendar Quarter  Robert  Excel Worksheet Functions  2  August 3rd 06 04:52 AM 
Need cell formula to subtotal gross by month for a quarter  mikeburg  Excel Discussion (Misc queries)  4  November 7th 05 09:25 PM 
Period to Period percentage change?  cs120  Excel Discussion (Misc queries)  1  September 18th 05 12:05 PM 
Formula to fill inthe Quarter  gls858  New Users to Excel  7  June 16th 05 05:53 PM 