#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LR LR is offline
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 634
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LR LR is offline
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Identifying unique dates in a row of cells containing dates... cdavidson Excel Discussion (Misc queries) 9 October 13th 06 08:43 PM
Identifying unique dates in a range of cells containing dates... cdavidson Excel Discussion (Misc queries) 4 October 13th 06 03:30 PM
calculating number of three month periods between two dates... neil Excel Discussion (Misc queries) 3 May 21st 06 01:52 PM
Setting quarter dates in excel RGB Excel Discussion (Misc queries) 5 May 15th 06 03:27 PM
need to convert list of dates to count no. of dates by week neowok Excel Worksheet Functions 13 January 30th 06 04:54 PM


All times are GMT +1. The time now is 01:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"