# round to nearest quarter year

#### areitk

##### New Member
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??

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Andrew Poulsom

##### MrExcel MVP
Without the workday bit:

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

#### areitk

##### New Member
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
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
Genius!
works like a charm....I appreciate you time and help andrew

#### Qqqqq

##### New Member
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
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.

Replies
2
Views
263
Replies
2
Views
246
Replies
0
Views
244
Replies
1
Views
335
Replies
3
Views
709

1,171,186
Messages
5,874,243
Members
433,041
Latest member
spongeavenger

### 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.

### Which adblocker are you using?

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

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