Thanks:  0
Likes:  0

# Thread: Change date to quarter

1. ## Change date to quarter

I'm looking for an efficient way to change a column of dates to a corresponding quarter.

The column looks like:
 10/10/2008 1/10/2006 7/10/2007 5/10/2006 6/10/2008 4/10/2006 11/10/2007 3/10/2006

I need it change to:
2008-Q4
2006-Q1
2007-Q3
2006-Q2
2008-Q2
2006-Q2
2007-Q4
2006-Q1

I tried Find/Replace but when I search for example 1/*/2006 and replace it with "2006-Q1", not only are January dates replaced but also November.

I'm not committed to using the "2006-Q1" format I just need someway to signify the year and quarter. Thank you very much for your help.

Joe

2. ## Re: Change date to quarter

Try

=YEAR(A1) & "-"&LOOKUP(MONTH(A1),{1,4,7,10},{"Q1","Q2","Q3","Q4"})

3. ## Re: Change date to quarter

Hello,

try

=YEAR(A1)&IF(MONTH(A1)<4,"-Q1",IF(MONTH(A1)<7,"-Q2",IF(MONTH(A1)<10,"-Q3","-Q4")))

4. ## Re: Change date to quarter

=YEAR(A1)&"-Q"&LOOKUP(MONTH(A1),{1,1;4,2;7,3;10,4})

EDIT: Beaten to it

5. ## Re: Change date to quarter

=year(a1)&"-Q"&-int(-month(a1)/3)

6. ## Re: Change date to quarter

Try the following:

Column A contains the dates u want to convert into quarters

Column B : =YEAR(A1)
COlumn C: =IF(MONTH(A1)<4,"Q1",IF(MONTH(A1)<7,"Q2",IF(MONTH(A1)<10,"Q3","Q4")))
Column D: =B1&"-"&C1

You can change the appearecnec of your result in column D

Hope it helps

7. ## Re: Change date to quarter

Also:
Code:
`=YEAR(A1)&" - Q"&INT((MONTH(A1)+2)/3)`
Looks like everybody had a Quarter formula...

8. ## Re: Change date to quarter

Works perfect. You folks are amazing. Thanks!

9. ## Re: Change date to quarter

I just used this too!!! thnx!!!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•