A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Formula - Period Quarter End



 
 
Thread Tools Display Modes
  #1  
Old August 24th 07, 01:44 AM posted to microsoft.public.excel.worksheet.functions
Sam
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 doesn’t work.

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

Any ideas???
Ads
  #2  
Old August 24th 07, 03:22 AM posted to microsoft.public.excel.worksheet.functions
Barb Reinhardt
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 doesn’t work.
>
> =IF(AND(LEFT(B54,3)>6,LEFT(B54,3)<10),006 / B35,"")
>
> Any ideas???

  #3  
Old August 24th 07, 05:04 AM posted to microsoft.public.excel.worksheet.functions
Sam
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 doesn’t work.
> >
> > =IF(AND(LEFT(B54,3)>6,LEFT(B54,3)<10),006 / B35,"")
> >
> > Any ideas???

  #4  
Old August 24th 07, 01:22 PM posted to microsoft.public.excel.worksheet.functions
Sam
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 doesn’t work.
> > >
> > > =IF(AND(LEFT(B54,3)>6,LEFT(B54,3)<10),006 / B35,"")
> > >
> > > Any ideas???

  #5  
Old August 27th 07, 03:34 AM posted to microsoft.public.excel.worksheet.functions
Sam
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 doesn’t work.
> >
> > =IF(AND(LEFT(B54,3)>6,LEFT(B54,3)<10),006 / B35,"")
> >
> > Any ideas???

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 01:19 AM.


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