Excel Formula Converting dates to quater ?

[Mike]

Hi all

Hope someone can help on this please

I have a large list of projects with various start and finish dates.

I have to insert a column which shows the yearly quarter works will be delivered. Could be in a single quarter or across quarters.

Any idea on a formula to do this

Example
Start Finish Delivered
1/4/2014 - 30 May 2014 = Q1
1/4/2014 - 15 July 2014 = Q1-Q2
1/05/2014 - 2 Feb 2015 = Q1-Q4

Quarter are

Quarter 1 = 01/04/2014 - 30/06/2014
Quarter 2 = 01/07/2014 - 30/09/2014
Quarter 3 = 01/10/2014 - 31/12/2014
Quarter 4= 01/01/2015 - 31/03/2015

Many thanks for you time and help

Regards

Mike

[Pauls9]

Try https://www.google.co.uk/search?num=...es+to+quarters

[Tali]

Try

=(SUBSTITUTE(ROUNDUP(MONTH(A1)/3,0)-1,0,4))*1

where A1 is the cell that contains the date.

ROUNDUP(MONTH(A1)/3,0) - finds the quarter, based on 1 Jan being Q1.

ROUNDUP(MONTH(A1)/3,0)-1 - finds the quarter and reduces by 1 to adjust for 1 April being your Q1

The substitute statement looks for 0 and replaces it with 4, so that 1 Jan is Q4.

The substitute command returns a string, so *1 forces Excel to treat the result as a number.

Hope that makes sense.

[Mike]

Originally Posted by Tali

Try

=(SUBSTITUTE(ROUNDUP(MONTH(A1)/3,0)-1,0,4))*1

where A1 is the cell that contains the date.

ROUNDUP(MONTH(A1)/3,0) - finds the quarter, based on 1 Jan being Q1.

ROUNDUP(MONTH(A1)/3,0)-1 - finds the quarter and reduces by 1 to adjust for 1 April being your Q1

The substitute statement looks for 0 and replaces it with 4, so that 1 Jan is Q4.

The substitute command returns a string, so *1 forces Excel to treat the result as a number.

Hope that makes sense.

Many thanks for help........

It works so that is great.....

Really appreciate the help

Regards

Mike