Quarter Date Format :

Hi Guys,

Many time we need to show Dates in QTR. Format , here is a trick to accomplish that task

Date Required Format
01-Jan-12 Q1-12
01-Apr-12 Q2-12
30-Sep-12 Q3-12
01-Oct-12 Q4-12

For this, i used Custom Format in  conditional formatting , you can see the below table for conditional formula and custom format

Suppose you have Dates in Column B , use below formula and Custom format

Required Conditional Formula Custom Format
1st Qtr =AND(MONTH(B2)>=1,MONTH(B2)<4) “Q1-“YY
2nd Qtr =AND(MONTH(B2)>=4,MONTH(B2)<7) “Q2-“YY
3rd Qtr =AND(MONTH(B2)>=7,MONTH(B2)<10) “Q3-“YY
4th Qtr =AND(MONTH(B2)>=10,MONTH(B2)<=12) “Q4-“YY

Download xlsx from here.

Thanks for reading

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s