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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,495
Messages
5,511,659
Members
408,859
Latest member
willm57

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top