Display a value between certain dates

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Roger Govier]

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

 

[Guest]

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.

 

[Guest]

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]

Hi Peter
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