round to nearest quarter year

[areitk]

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

 

[Andrew Poulsom]

Without the workday bit:

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

 

[areitk]

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]

Try:

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

The WORKDAY function requires that the Analysis ToolPak is installed.

 

[areitk]

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

 

[Qqqqq]

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]

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.