Creating Date Range in Excel for Quarterly Reporting

I'm trying to get a report to run based on Quarters.  I need the Quarter date to display based on a date in another cell.  I Currently have:

=IF(ROUNDUP(MONTH(D2)/3,0)=1,"01/01/11..03/31/11",IF(ROUNDUP(MONTH(D2)/3,0)=2,"04/01/11..06/30/11",IF(ROUNDUP(MONTH(D2)/3,0)=3,"07/01/11..09/30/11","10/01/10..12/31/11")))

However, I have to go in each year to change the year.  I would like the year to change automatically.
I need the result to display as: 1/1/2011..3/31/2011 (which would change according to the quarter)
babstxAsked:
Who is Participating?
 
jppintoCommented:
You can change your formula to this:

=IF(ROUNDUP(MONTH(D2)/3,0)=1,"01/01/" & RIGHT(YEAR(D2),2)&"..03/31/"& RIGHT(YEAR(D2),2),IF(ROUNDUP(MONTH(D2)/3,0)=2,"04/01/"&RIGHT(YEAR(D2),2)&"..06/30/"& RIGHT(YEAR(D2),2),IF(ROUNDUP(MONTH(D2)/3,0)=3,"07/01/"&RIGHT(YEAR(D2),2)&"..09/30/"&RIGHT(YEAR(D2),2),"10/01/"&RIGHT(YEAR(D2),2)&"..12/31/"&RIGHT(YEAR(D2),2))))

jppinto
0
 
jppintoCommented:
Working example attached...
Book4.xls
0
 
babstxAuthor Commented:
Thank you!
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Patrick MatthewsCommented:
This one is a bit shorter :)

=TEXT(DATE(YEAR(D2),CHOOSE(MONTH(D2),1,1,1,4,4,4,7,7,7,10,10,10),1),"mm/dd/yy")&".."&TEXT(DATE(YEAR(D2),CHOOSE(MONTH(D2),1,1,1,4,4,4,7,7,7,10,10,10)+3,0),"mm/dd/yy")
0
 
jppintoCommented:
:) good work matthew...
0
 
Patrick MatthewsCommented:
Shorter still:

=TEXT(DATE(YEAR(D5),3*ROUNDUP(MONTH(D5)/3,0)-2,1),"mm/dd/yy")&".."&TEXT(DATE(YEAR(D5),3*ROUNDUP(MONTH(D5)/3,0)+1,0),"mm/dd/yy")
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.