round to nearest quarter year

areitk

New Member
Joined
Jul 2, 2010
Messages
11
Hello, I am looking for some help on rounding dates to the nearest (previous) quarter.
The 4 days where the quarters fall are mar1, june1, sept1, dec1

For example:
I have june3, 2008 and i want june 1,2008
I have nov 30,2010 and i want sept 1,2010

Would be a bonus if you could adjust so that it was a workweek day.
i.e. if june1 2008 is a monday then fine. But if june 1, 2010 is a sunday then i need to to go to next workday so answer would be june 2 for 2010

is this too complicated??

Thx very much in advance
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

areitk

New Member
Joined
Jul 2, 2010
Messages
11
Thats awesome! Thx Andrew!

Could anyone else help to make it fall on a workday? PLEASE....let me explain:
Any way could make it so the beginning of a quarter on any particular year is a workday?
And if the beginning of the quarter is not on a workday, that it go to the first workday right after?

I.e. March 1, 2009 is a sunday
So with your formula I could put in may 20, 2009 and it would (appropriately) give me march 1, 2009
But since its a sunday i need the formula to give me the monday - March 2, 2009
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

=WORKDAY(DATE(YEAR(A1),MONTH(A1)-MOD(MONTH(A1),3),0),1)

The WORKDAY function requires that the Analysis ToolPak is installed.
 

areitk

New Member
Joined
Jul 2, 2010
Messages
11

ADVERTISEMENT

Genius!
works like a charm....I appreciate you time and help andrew :)
 

Qqqqq

New Member
Joined
Feb 6, 2014
Messages
48
Hello, I am looking for some help on rounding dates to the nearest (previous) quarter.

I realize this is an old thread, but in case someone is looking for another solution, the following options also work...

Assuming your start date is in cell A1, and your end date is in cell B1:

=MROUND(YEARFRAC(A1,B1,3),0.25)

or

=ROUND(YEARFRAC(A1,B1,3),2)
 

Qqqqq

New Member
Joined
Feb 6, 2014
Messages
48
Disregard my earlier post... the formulas I provided will give you the number of quarters between two dates. They will not round a date for you. For that, you are better off using one of the previously posted answers.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,679
Messages
5,654,713
Members
418,149
Latest member
tjanok

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top