| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate a date based on period and year
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate a date based on period and year
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate a date based on period and year
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate a date based on period and year
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate a date based on period and year
You're welcome, thanks for the feedback.
"Sam" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I change date based on year | Excel Worksheet Functions | |||
| calculate prior calendar year end from a moving current date | Excel Worksheet Functions | |||
| Sum column based on value in each row, if two cells equal, or if date is within time period | Excel Worksheet Functions | |||
| how to calculate $1000/year at 5% for 25 years showing each year | Setting up and Configuration of Excel | |||
| Count returns for a period - year | Excel Worksheet Functions | |||
Linear Mode
