Cable Forum - View Single Post - Excel Formula Converting dates to quater ?

Tali · 16-09-2014, 12:05

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.

16-09-2014, 12:05	#3
Tali Inactive Join Date: Sep 2007 Location: West Mids Posts: 286	Re: Excel Formula Converting dates to quater ? 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.