Display a value between certain dates
 

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.

Display a value between certain dates
 

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.

Display a value between certain dates
 

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.

Display a value between certain dates
 

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

Display a value between certain dates
 

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.

Display a value between certain dates
 

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.

Display a value between certain dates
 

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

Display a value between certain dates
 

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.