Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Formula Converts Date To Quarter & Year

1. rhc
I agreed to these rules
Join Date
19th April 2006
Posts
19

## Formula Converts Date To Quarter & Year

A1 contains a date formatted cell as dd/mm/yr, say 01/15/06. What formula, in B1, would convert this date to: Q1-06?

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Formula Converts Date To Quarter & Year

="Q"&INT((MONTH(A1)/4)+1)&"-"&RIGHT(YEAR(A1),2)

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: Formula Converts Date To Quarter & Year

rhc,

Or, if you prefer, you can use a custom cell format like below and eliminate the need for a formula.
"Q1-" yy
Jim

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Formula Converts Date To Quarter & Year

Jim, that gives Q1-06 even for a date in September...

Excel Video Tutorials / Excel Dashboards Reports

5. rhc
I agreed to these rules
Join Date
19th April 2006
Posts
19

## Re: Formula Converts Date To Quarter & Year

Originally Posted by ByTheCringe2

="Q"&INT((MONTH(A1)/4)+1)&"-"&RIGHT(YEAR(A1),2)

Works beautifully.

Thanks so much!

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Formula Converts Date To Quarter & Year

Originally Posted by ByTheCringe2
Jim, that gives Q1-06 even for a date in September...
True, you would need four formats. Works if you are talking column headers and such but not as well if you just have random dates you want to sort or something. Just thought I'd toss it out there.

Jim

Excel Video Tutorials / Excel Dashboards Reports

7. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,477

## Re: Formula Converts Date To Quarter & Year

Hi,

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

HTH

8. I agreed to these rules
Join Date
21st February 2012
Posts
1

## Re: Formula Converts Date To Quarter & Year

Slight problem with the formula.

This one works better:

="Q"&INT((MONTH(A1)-1)/3+1)&" - CY"&YEAR(A1)

You can ignore my change for the 'year' part. The part I'm fixing is the Month calculation.

Originally Posted by rhc
Works beautifully.

Thanks so much!

Excel Video Tutorials / Excel Dashboards Reports

9. I agreed to these rules
Join Date
17th May 2012
Posts
1

## Re: Formula Converts Date To Quarter & Year

I like using roundup() to alleviate the +1/-1 stuff:

="Q"&ROUNDUP(MONTH(A1)/3,0)&"-"&RIGHT(YEAR(A1),2)

Excel Video Tutorials / Excel Dashboards Reports

10. I agreed to these rules
Join Date
14th May 2016
Posts
1

## Re: Formula Converts Date To Quarter & Year

Originally Posted by rhc
A1 contains a date formatted cell as dd/mm/yr, say 01/15/06. What formula, in B1, would convert this date to: Q1-06?
My date is in cell D2 and formatted MM/DD/YYYY.
For the result I prefer to have the year displayed first and the quarter following so that it sorts chronologically.
Try this =YEAR(D2)&"-Q"&ROUNDUP(MONTH(D2)/3,0)

Excel Video Tutorials / Excel Dashboards Reports

There are currently 3 users browsing this thread. (0 members and 3 guests)

#### Posting Permissions

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