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.

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.

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.

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

Use 2 empty cols say, cols D & E to create a vlookup table

Input these 4 initializing entries:

In D1: 01-Jan-2007
In D2: 01-Apr-2007
In E1: 1
In E2: 2

Select D1:E2, copy down as far as required to fill the series

Then we could place in say, B1, and copy down:
=IF(A1<--"1-Jan-2007","",VLOOKUP(A1,D:E,2))
to return the required "quarterly" numbers
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Peterp" wrote:
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

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.

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.

"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.

Hi Peter

=INT((MONTH(A1)+11*(MONTH(A1)=1)-1/3+1
For getting things in the normal Quarter of the year is missing some
brackets, and should be
=INT((MONTH(A1)+11*(MONTH(A1)=1)-1)/3)+1

but it doesn't work when the date in F1 is Jan 08

All the formula needs to be is
=INT((MONTH(F1)-1)/3)+1

--
Regards

Roger Govier


"Peterp" wrote in message
...
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.