Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Forward & Backwards Quarters from an Entered Date
I need column names for quarters 4 quarters forward and 3 quarters backwards
along with the present from the an entered date as shown below: 3Qtr06 4Qtr06 1Qtr07 2Qtr07 3Qtr07 4Qtr07 1Qtr08 2Qtr08 3Qtr08 I believe the issue I am running into is leap years . Depending on the month (which ends up being a first quarter or last quarter month that causes issues), the most future quarter or the oldest quarter end up with the wrong year. I ended up having the user enter the month & the year from two drop downs. The reason I went to drop downs is that so I could set the actual day of the month for both the year and the month to try to avoid the issue, with no luck. I run out to 2012. I ended up having the user enter the month & the year from two drop downs. Here are some of the calculations that I am am using: ROUNDUP(MONTH($F$3)/3,0)&"Qtr"&RIGHT((YEAR($F$4)),2) for present qtr ROUNDUP(MONTH($F$3+91)/3,0)&"Qtr"&RIGHT((YEAR($F$4+91)),2) for 2qtr out ROUNDUP(MONTH($F$3+182)/3,0)&"Qtr"&RIGHT((YEAR($F$4+182)),2) for 3 qtr out Where $F$3 is the month and $f$4 is the year Thanks for the help in advance, Danny |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Forward & Backwards Quarters from an Entered Date
=ROUNDUP($F$3/3,0)&" Qtr"&$F$4
"Danny" wrote: I need column names for quarters 4 quarters forward and 3 quarters backwards along with the present from the an entered date as shown below: 3Qtr06 4Qtr06 1Qtr07 2Qtr07 3Qtr07 4Qtr07 1Qtr08 2Qtr08 3Qtr08 I believe the issue I am running into is leap years . Depending on the month (which ends up being a first quarter or last quarter month that causes issues), the most future quarter or the oldest quarter end up with the wrong year. I ended up having the user enter the month & the year from two drop downs. The reason I went to drop downs is that so I could set the actual day of the month for both the year and the month to try to avoid the issue, with no luck. I run out to 2012. I ended up having the user enter the month & the year from two drop downs. Here are some of the calculations that I am am using: ROUNDUP(MONTH($F$3)/3,0)&"Qtr"&RIGHT((YEAR($F$4)),2) for present qtr ROUNDUP(MONTH($F$3+91)/3,0)&"Qtr"&RIGHT((YEAR($F$4+91)),2) for 2qtr out ROUNDUP(MONTH($F$3+182)/3,0)&"Qtr"&RIGHT((YEAR($F$4+182)),2) for 3 qtr out Where $F$3 is the month and $f$4 is the year Thanks for the help in advance, Danny |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Forward & Backwards Quarters from an Entered Date
returns the number value vs month
"Teethless mama" wrote: =ROUNDUP($F$3/3,0)&" Qtr"&$F$4 "Danny" wrote: I need column names for quarters 4 quarters forward and 3 quarters backwards along with the present from the an entered date as shown below: 3Qtr06 4Qtr06 1Qtr07 2Qtr07 3Qtr07 4Qtr07 1Qtr08 2Qtr08 3Qtr08 I believe the issue I am running into is leap years . Depending on the month (which ends up being a first quarter or last quarter month that causes issues), the most future quarter or the oldest quarter end up with the wrong year. I ended up having the user enter the month & the year from two drop downs. The reason I went to drop downs is that so I could set the actual day of the month for both the year and the month to try to avoid the issue, with no luck. I run out to 2012. I ended up having the user enter the month & the year from two drop downs. Here are some of the calculations that I am am using: ROUNDUP(MONTH($F$3)/3,0)&"Qtr"&RIGHT((YEAR($F$4)),2) for present qtr ROUNDUP(MONTH($F$3+91)/3,0)&"Qtr"&RIGHT((YEAR($F$4+91)),2) for 2qtr out ROUNDUP(MONTH($F$3+182)/3,0)&"Qtr"&RIGHT((YEAR($F$4+182)),2) for 3 qtr out Where $F$3 is the month and $f$4 is the year Thanks for the help in advance, Danny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date going forward | Excel Worksheet Functions | |||
Date To Quarter, Plus Next 3 Quarters | Excel Worksheet Functions | |||
Auto calculate for date + days forward to yield new date | Excel Worksheet Functions | |||
Page forward goes backwards | Excel Discussion (Misc queries) | |||
How do I calculate the number of quarters between two date | Excel Discussion (Misc queries) |