Calculate a date based on period and year

[Sam]

I am trying to find a formula to give me the Previous Quarter End. The
headers on the spreadsheet look as follow:

Acct # Curr Period Prev Qtr End Prev Period Curr Period Prev Yr
2134 007 ? 006 007 / 2006

The report is refreshed frequently, but here is a sample of how the data
format that is populated for the date fields.

Period: 007.2007
Year: 2007

There are 12 periods in the year and 4 quarter ends (The quarter end are
March, June, September, and December)

I have formulas for all the fields except the previous quarter end (12 /
2006, 003 / 2007, 006 / 2007, 009/2007). In the example above, the previous
quarter end date would 006 / 2007.

Any clues.

 

[Trevor Shuttleworth]

Let's say the current period is in cell A1

The current quarter could be derived as:

=ROUNDUP(A1/3,0)

and the previous quarter would be:

=IF(ROUNDUP(A1/3,0)=1,4,ROUNDUP(A1/3,0)-1)

If the previous quarter is 4, then the previous year is current year -1

The quarter end would be 3 x quarter: 3 x Q1 = 3 = March; 3 x Q2 = 6 = June;
etc

Similarly for previous quarter end.

Not sure if that helps or works with your existing formulae

Regards

Trevor

 

[Sam]

I put in the formula you suggested and received an answer of 2. If this
means the 2nd quarter than it returned the correct answer as the current
period on my worksheet is 07. However, I need the answer to be expressed as
a period and year. Therefore, 2nd quarter needs to be expressed as 006/2007.
It should always take the last month of the quarter.

Expressing the results in this format (006/2007) is particularly important
when the report is pulled in the first quarter of any given year, which would
make the previous quarter also the previous year. For example, if the report
were pulled for period 002/2007 the previous quarter would be 012/2006.

How can I change the formula to show the period and year (format - 006/2007)?

Is this possible?

Thanks.

 

[Trevor Shuttleworth]

Current Quarter End would be:

=TEXT(ROUNDUP(A1/3,0)*3,"000") & " / " & YEAR(NOW())

Previous Quarter End would be:

=IF(ROUNDUP(A1/3,0)=1,"012 /
"&YEAR(NOW())-1,TEXT((ROUNDUP(A1/3,0)-1)*3,"000")&" / "&YEAR(NOW()))

Regards

Trevor

 

[Sam]

It worked...thanks.

Your the best.

Current Quarter End would be:

=TEXT(ROUNDUP(A1/3,0)*3,"000") & " / " & YEAR(NOW())

Previous Quarter End would be:

=IF(ROUNDUP(A1/3,0)=1,"012 /
"&YEAR(NOW())-1,TEXT((ROUNDUP(A1/3,0)-1)*3,"000")&" / "&YEAR(NOW()))

Regards

Trevor

 

[Trevor Shuttleworth]

You're welcome, thanks for the feedback.

It worked...thanks.

Your the best.