Calculate a date based on period and year
It worked...thanks.
Your the best.
"Trevor Shuttleworth" wrote:
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" wrote in message
...
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" wrote:
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" wrote in message
...
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.
|