• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

First day of the quarter

K

kumarAmit

New Member
Hi,


I have list of data having dates. This data is having dates for 5-7 years.


I want to figure out 1st data of the quarter. For e.g, there are dates from 8-Nov-2007 to 28-oct-2012. I want 1-oct-2007 as result.


Data is dynamic and it changes.


Amit
 
@Amit... Welcome to Chandoo.org and thanks for posting your question.


Please take a look at our forums home page and sticky threads to understand our rules & policies.


Regarding your question,


If you just want the very first quarter start date

(that is for all dates between 2007 and 2012, you just want one output - 1-oct-2007):


Then, use this formula.


Assuming your dates start in cell B2, write

=DATE(YEAR(B2),CHOOSE(MONTH(B2),1,1,1,4,4,4,7,7,7,10,10,10),1)


This will give you 1-oct-2007 as output.


If you want nearest quarter date for each date

Then use the same formula in C2 (or any other adjacent column) and drag it down.
 
Hi,


Thanks for your reply.


I think I couldn't explain my question correctly.


I have dates in cell A5 to A300. These cells are having dates.


I want to figure out 1st date of the quarter. For e.g, there are dates from 8-Nov-2007 to 28-oct-2012. I want 1-oct-2007 as result.


Your formula works fine 1st date of the quarter needs to be figure out from 1 cell.


Thanks again for last reply.


Amit
 
Hi Amit ,


You need to clarify in more detail ; are your dates in A5:A300 sorted in any order , or are they randomly arranged ?


If they are sorted in ascending order , it means that the date in A5 is the earliest date , and the formula to find out the quarter needs to look at only this one cell.


If they are randomly arranged , then the formula has to find out the earliest date in the range A5:A300 , by using the MIN function , and then use this earliest date to arrive at the quarter.


Can you clarify ?


Narayan
 
Hi Amit


You can slightly modify the formula mentioned by Chandoo as follows:


=DATE(YEAR(min(A5:A300)),CHOOSE(MONTH(min(A5:A300)),1,1,1,4,4,4,7,7,7,10,10,10),1)


Cheers,

Amritansh
 
You must log in or register to reply here.
Back
Top