Home 
Search 
Today's Posts 
#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??? 
#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??? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Excel should have a QUARTER formula for DATE calculations  Excel Worksheet Functions  
Formula for Calendar Quarter  Excel Worksheet Functions  
Need cell formula to subtotal gross by month for a quarter  Excel Discussion (Misc queries)  
Period to Period percentage change?  Excel Discussion (Misc queries)  
Formula to fill inthe Quarter  New Users to Excel 