ExcelBanter - View Single Post

Peterp

"Peterp" wrote:

Thanks Max - your Quarterly formula did the the trick.

Thanks Roger,
Your months value from Feb worked 100% but as your quarters were based up
the results of your months, the quarters did not align with actual quarters
of the year.

IE:Jan-Mar, Apr-Jun etc as the months above start in Feb as I requested.

I modified your "Quarter in a single cell" formula to:

=INT((MONTH(A1)+11*(MONTH(A1)=1)-1/3)+1

That seemed to do the trick and works well.

Thanks very much for your help.

"Roger Govier" wrote:

Hi Peter

In addition to Max's method you could use
to get Months in B1
=MONTH(A1)-1+12*(MONTH(A1)=1)
to get Quarters, in C1
=INT((B1-1)/3)+1

or if you wanted only the quarter in a single cell then
=INT((MONTH(A1)-1+12*(MONTH(A1)=1)-1)/3)+1

copy down as required

--
Regards

Roger Govier

"Peterp" wrote in message
...
GREAT STUFF!
This works100% - thanks very much.
This now gives me the period for a certain sequence of monthly events.
I may be pushing it but would it be possible to modify this formula to
give
me a similar value for a 3 month period (quarterly).
IE: Dates between 01Jan07 - 31Mar07 = 1
01Apr07 - 30Jun07 = 2 etc

"Max" wrote:

One way ..

Assume real dates running in A1 down,
dates assumed from 2007 onwards

Put in B1:
=IF(A1< --"1-Jan-2007","",DATEDIF("1-Jan-2007",A1,"m"))
Copy B1 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Peterp" wrote:
Hi
If dates are in A1:A100.
I want to place a value in B1:B100.
IE: all dates between 1 Feb07 and 28feb07 I would like to see a "1"
in
B1:B100.
All dates between 1Mar07 and 31Mar07 a "2" etc.
I have tried various "IF" fomulae but none of then work.
Any suggestions would be appreciated.