Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 doesnt work. =IF(AND(LEFT(B54,3)6,LEFT(B54,3)<10),006 / B35,"") Any ideas??? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 doesnt work. =IF(AND(LEFT(B54,3)6,LEFT(B54,3)<10),006 / B35,"") Any ideas??? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 doesnt work. =IF(AND(LEFT(B54,3)6,LEFT(B54,3)<10),006 / B35,"") Any ideas??? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 doesnt work. =IF(AND(LEFT(B54,3)6,LEFT(B54,3)<10),006 / B35,"") Any ideas??? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 doesnt 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 |