Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam Sam is offline
external usenet poster
 
Posts: 699
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam Sam is offline
external usenet poster
 
Posts: 699
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam Sam is offline
external usenet poster
 
Posts: 699
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam Sam is offline
external usenet poster
 
Posts: 699
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 10: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


All times are GMT +1. The time now is 09:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"