Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quarter Dates
I have a column A which can have different dates.
As July 07 Feb 08 Apr 08 May 08 Oct 08 Nov 09 In column B I would like to have dates that correspond to the quarterly periods Dec, March, June,September. For example in column B, I would like to see June 07 March 08 march 08 June 08 etc. Is there a formula that could be applied to column B to get the dates as above. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quarter Dates
Hi
Try entering in B1 =TEXT(DATE(YEAR(A1),(INT((MONTH(A1)-1)/3)+1)*3,1),"mmm yy") and copy down -- Regards Roger Govier "LR" wrote in message ups.com... I have a column A which can have different dates. As July 07 Feb 08 Apr 08 May 08 Oct 08 Nov 09 In column B I would like to have dates that correspond to the quarterly periods Dec, March, June,September. For example in column B, I would like to see June 07 March 08 march 08 June 08 etc. Is there a formula that could be applied to column B to get the dates as above. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quarter Dates
I think he wants
=DATE(YEAR(A1),(INT((MONTH(A1)-4)/3)+1)*3,1) formatted as mmm-yy -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Try entering in B1 =TEXT(DATE(YEAR(A1),(INT((MONTH(A1)-1)/3)+1)*3,1),"mmm yy") and copy down -- Regards Roger Govier "LR" wrote in message ups.com... I have a column A which can have different dates. As July 07 Feb 08 Apr 08 May 08 Oct 08 Nov 09 In column B I would like to have dates that correspond to the quarterly periods Dec, March, June,September. For example in column B, I would like to see June 07 March 08 march 08 June 08 etc. Is there a formula that could be applied to column B to get the dates as above. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quarter Dates
Bob, I had thought about -4 rather than -1, but his example of
Jul 07 Jun 07 Feb 08 Mar 08 etc. rather threw all options, so I had decided his examples were not real. You are probably correct though, and he may just have missed out one of his months in the second set of values -- Regards Roger Govier "Bob Phillips" wrote in message ... I think he wants =DATE(YEAR(A1),(INT((MONTH(A1)-4)/3)+1)*3,1) formatted as mmm-yy -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Try entering in B1 =TEXT(DATE(YEAR(A1),(INT((MONTH(A1)-1)/3)+1)*3,1),"mmm yy") and copy down -- Regards Roger Govier "LR" wrote in message ups.com... I have a column A which can have different dates. As July 07 Feb 08 Apr 08 May 08 Oct 08 Nov 09 In column B I would like to have dates that correspond to the quarterly periods Dec, March, June,September. For example in column B, I would like to see June 07 March 08 march 08 June 08 etc. Is there a formula that could be applied to column B to get the dates as above. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quarter Dates
It was definitely confusing Roger.
"Roger Govier" wrote in message ... Bob, I had thought about -4 rather than -1, but his example of Jul 07 Jun 07 Feb 08 Mar 08 etc. rather threw all options, so I had decided his examples were not real. You are probably correct though, and he may just have missed out one of his months in the second set of values -- Regards Roger Govier |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quarter Dates
If your dates are real dates then you may want to consider a Pivot table
which will group into Quarters in a heartbeat. All depends on your data really, but once you've been there you won't look back. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------- It's easier to beg forgiveness than ask permission :-) "LR" wrote: I have a column A which can have different dates. As July 07 Feb 08 Apr 08 May 08 Oct 08 Nov 09 In column B I would like to have dates that correspond to the quarterly periods Dec, March, June,September. For example in column B, I would like to see June 07 March 08 march 08 June 08 etc. Is there a formula that could be applied to column B to get the dates as above. Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quarter Dates
Thanks guys. But the examples are real. What I wanted is if there is a
month which falls outside the designated quarter months, then it should revert back to one of the quarter months So you could have any set of scenarios of dates falling outside the quarter months. For example if the date is in july, or august it should revert back to either June or september. Or if the date is january or February it should revert back to either December or March as quarter period. This is an acutal scenario. LR wrote: I have a column A which can have different dates. As July 07 Feb 08 Apr 08 May 08 Oct 08 Nov 09 In column B I would like to have dates that correspond to the quarterly periods Dec, March, June,September. For example in column B, I would like to see June 07 March 08 march 08 June 08 etc. Is there a formula that could be applied to column B to get the dates as above. Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quarter Dates
What is the relevance of an actual scenario? What Roger and I gave applies
to test data, real data, imaginary data, etc. They are solutions to a problem, so they either work or they don't. If they do, great, if they don't, in what way? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "LR" wrote in message ups.com... Thanks guys. But the examples are real. What I wanted is if there is a month which falls outside the designated quarter months, then it should revert back to one of the quarter months So you could have any set of scenarios of dates falling outside the quarter months. For example if the date is in july, or august it should revert back to either June or september. Or if the date is january or February it should revert back to either December or March as quarter period. This is an acutal scenario. LR wrote: I have a column A which can have different dates. As July 07 Feb 08 Apr 08 May 08 Oct 08 Nov 09 In column B I would like to have dates that correspond to the quarterly periods Dec, March, June,September. For example in column B, I would like to see June 07 March 08 march 08 June 08 etc. Is there a formula that could be applied to column B to get the dates as above. Thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quarter Dates
Hi
For example if the date is in july, or august it should revert back to either June or september You can only have one - it is either going back to the previous quarter date which Bob's solution provides or - it goes forward to the next quarter date, which my solution provides July, August and September can all be treated as June, or they can all be treated as September -- Regards Roger Govier "LR" wrote in message ups.com... Thanks guys. But the examples are real. What I wanted is if there is a month which falls outside the designated quarter months, then it should revert back to one of the quarter months So you could have any set of scenarios of dates falling outside the quarter months. For example if the date is in july, or august it should revert back to either June or september. Or if the date is january or February it should revert back to either December or March as quarter period. This is an acutal scenario. LR wrote: I have a column A which can have different dates. As July 07 Feb 08 Apr 08 May 08 Oct 08 Nov 09 In column B I would like to have dates that correspond to the quarterly periods Dec, March, June,September. For example in column B, I would like to see June 07 March 08 march 08 June 08 etc. Is there a formula that could be applied to column B to get the dates as above. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identifying unique dates in a row of cells containing dates... | Excel Discussion (Misc queries) | |||
Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
calculating number of three month periods between two dates... | Excel Discussion (Misc queries) | |||
Setting quarter dates in excel | Excel Discussion (Misc queries) | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions |