Thread: Excel Formula Converting dates to quater ?
View Single Post
Old 17-09-2014, 07:26   #4
Mike
Inactive
 
Join Date: Jan 2004
Age: 63
Posts: 2,242
Mike is the helpful oneMike is the helpful oneMike is the helpful oneMike is the helpful oneMike is the helpful oneMike is the helpful oneMike is the helpful oneMike is the helpful oneMike is the helpful oneMike is the helpful oneMike is the helpful oneMike is the helpful oneMike is the helpful oneMike is the helpful oneMike is the helpful oneMike is the helpful oneMike is the helpful one
Re: Excel Formula Converting dates to quater ?
Quote:
Originally Posted by Tali View Post
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
Mike is offline   Reply With Quote