Customized Formula

[Learning Excel]

I'm in need of a very specific formula please.
A1 to A100 are all different dates as 02/17/08 ( m,d,y) (could also omitt
the year)
B1 to B100 will be formulas ( of course I just need B1 formula so I copy it
down)
Something like : if A1=1/2/08
B1 will show "FHFQ"
-- Probably it has to show in C1 as B1 has the formula (well, it doesn't
matter to me either way).
FHFQ means "first half first quarter" of the year of course.
So everytime a date is enter in A1 to A100, B or C columns(as you wish) will
show the previous text if dates are from 1/1/8 to 2/15/08.
If I enter 3/1/08 then it will show "SHFQ" meaning "second half of first
quarter".
It's very complicated to me because the need of setting a range in the
formula and also how the text will appear accordinly to the range the date
is in.
By the way the year does not matter as is the same for me any year.
Just the day and the month.
Thanks a lot in advance.

Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.

 

[Niek Otten]

In D1:
0
In D2:
2/15/2008
In D3:
4/1/2008
etc
In D9:
12/31/2008
In E1:
=TEXT(D1,"mmdd")
Copy down to E9
In F1:
FHFQ
In F2:
SHFQ
etc
Now your search formula is:
=VLOOKUP(TEXT(A1,"mmdd"),$E$1:$F$9,2)
Copy down as far as you need

You may have different definitions of the beginning of a quarter; 14th, 15th or 16th of the month. Change table in column D
accordingly.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'm in need of a very specific formula please.
| A1 to A100 are all different dates as 02/17/08 ( m,d,y) (could also omitt
| the year)
| B1 to B100 will be formulas ( of course I just need B1 formula so I copy it
| down)
| Something like : if A1=1/2/08
| B1 will show "FHFQ"
| -- Probably it has to show in C1 as B1 has the formula (well, it doesn't
| matter to me either way).
| FHFQ means "first half first quarter" of the year of course.
| So everytime a date is enter in A1 to A100, B or C columns(as you wish) will
| show the previous text if dates are from 1/1/8 to 2/15/08.
| If I enter 3/1/08 then it will show "SHFQ" meaning "second half of first
| quarter".
| It's very complicated to me because the need of setting a range in the
| formula and also how the text will appear accordinly to the range the date
| is in.
| By the way the year does not matter as is the same for me any year.
| Just the day and the month.
| Thanks a lot in advance.
|
| Socrates said: I only know, I don''''t know nothing.
| I say : I don''''t even know, I don''''t
| know nothing.

 

[Niek Otten]

In fact, D1 might be 1/1/2008; that's more "natural".
You have to be sure there is no time in your date, otherwise you'll have to end your E column in a different way.
Post back if you have problems

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| In D1:
| 0
| In D2:
| 2/15/2008
| In D3:
| 4/1/2008
| etc
| In D9:
| 12/31/2008
| In E1:
| =TEXT(D1,"mmdd")
| Copy down to E9
| In F1:
| FHFQ
| In F2:
| SHFQ
| etc
| Now your search formula is:
| =VLOOKUP(TEXT(A1,"mmdd"),$E$1:$F$9,2)
| Copy down as far as you need
|
| You may have different definitions of the beginning of a quarter; 14th, 15th or 16th of the month. Change table in column D
| accordingly.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| || I'm in need of a very specific formula please.
|| A1 to A100 are all different dates as 02/17/08 ( m,d,y) (could also omitt
|| the year)
|| B1 to B100 will be formulas ( of course I just need B1 formula so I copy it
|| down)
|| Something like : if A1=1/2/08
|| B1 will show "FHFQ"
|| -- Probably it has to show in C1 as B1 has the formula (well, it doesn't
|| matter to me either way).
|| FHFQ means "first half first quarter" of the year of course.
|| So everytime a date is enter in A1 to A100, B or C columns(as you wish) will
|| show the previous text if dates are from 1/1/8 to 2/15/08.
|| If I enter 3/1/08 then it will show "SHFQ" meaning "second half of first
|| quarter".
|| It's very complicated to me because the need of setting a range in the
|| formula and also how the text will appear accordinly to the range the date
|| is in.
|| By the way the year does not matter as is the same for me any year.
|| Just the day and the month.
|| Thanks a lot in advance.
||
|| Socrates said: I only know, I don''''t know nothing.
|| I say : I don''''t even know, I don''''t
|| know nothing.
|
|

 

[Barb Reinhardt]

An alternate way would be to use this formula

=IF(MONTH(A1)<=3,"FHFQ",IF(MONTH(A1)<=6,"FHSQ",IF(MONTH(A1)<=9,"SH1Q","SHSQ")))

where A1 contains the date.

 

[WLMPilot]

I don't know what all your date ranges will be, but this should get you
started and I don't know if you need it for the entire year:

A1: Date
B1: (formula)
=IF(AND(A1>=DATEVALUE("1/1/2008"),A1<=DATEVALUE("2/15/2008")),"FHFQ",IF(AND(A1>=DATEVALUE("2/16/2008"),A1<=DATEVALUE("3/1/2008")),"FHSQ","NOTHING")

To continue this out, replace "NOTHING" with the IF statement as shown for
the previous check and continue until you have covered all quarters.
Example, if I just wanted to cover FHFQ - SHSQ, the formula would look like
this:

=IF(AND(A1>=DATEVALUE("1/1/2008"),A1<=DATEVALUE("2/15/2008")),"FHFQ",IF(AND(A1>=DATEVALUE("2/16/2008"),A1<=DATEVALUE("3/1/2008")),"FHSQ",IF(AND(A1>=DATEVALUE("3/2/2008"),A1<=DATEVALUE("4/15/2008")),"SHFQ","SHSQ)

Obviously, adjust the dates as needed.

Les

 

[Learning Excel]

Thanks Niek,Barb and WLMPilot for these wonderfull answers, all of them are
very usefull and applicable to my worksheet.
Can't complain, asked for one and got 3.
THANKS!

 

[Learning Excel]

Could I use your formula without the year, WLMPilot?
How do I take off 2008 and will apply to any year the same.

 

[Niek Otten]

Hi Les,

I don't think you tested that.
Certainly not for 8 half quarters (with Excel 2003 and older), but apparently not even for (example) Feb 17, I think.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I don't know what all your date ranges will be, but this should get you
| started and I don't know if you need it for the entire year:
|
| A1: Date
| B1: (formula)
|
=IF(AND(A1>=DATEVALUE("1/1/2008"),A1<=DATEVALUE("2/15/2008")),"FHFQ",IF(AND(A1>=DATEVALUE("2/16/2008"),A1<=DATEVALUE("3/1/2008")),"FHSQ","NOTHING")
|
| To continue this out, replace "NOTHING" with the IF statement as shown for
| the previous check and continue until you have covered all quarters.
| Example, if I just wanted to cover FHFQ - SHSQ, the formula would look like
| this:
|
|
=IF(AND(A1>=DATEVALUE("1/1/2008"),A1<=DATEVALUE("2/15/2008")),"FHFQ",IF(AND(A1>=DATEVALUE("2/16/2008"),A1<=DATEVALUE("3/1/2008")),"FHSQ",IF(AND(A1>=DATEVALUE("3/2/2008"),A1<=DATEVALUE("4/15/2008")),"SHFQ","SHSQ)
|
| Obviously, adjust the dates as needed.
|
| Les
|
|
|
|
|
|
| "Learning Excel" wrote:
|
| > I'm in need of a very specific formula please.
| > A1 to A100 are all different dates as 02/17/08 ( m,d,y) (could also omitt
| > the year)
| > B1 to B100 will be formulas ( of course I just need B1 formula so I copy it
| > down)
| > Something like : if A1=1/2/08
| > B1 will show "FHFQ"
| > -- Probably it has to show in C1 as B1 has the formula (well, it doesn't
| > matter to me either way).
| > FHFQ means "first half first quarter" of the year of course.
| > So everytime a date is enter in A1 to A100, B or C columns(as you wish) will
| > show the previous text if dates are from 1/1/8 to 2/15/08.
| > If I enter 3/1/08 then it will show "SHFQ" meaning "second half of first
| > quarter".
| > It's very complicated to me because the need of setting a range in the
| > formula and also how the text will appear accordinly to the range the date
| > is in.
| > By the way the year does not matter as is the same for me any year.
| > Just the day and the month.
| > Thanks a lot in advance.
| >
| > Socrates said: I only know, I don''''t know nothing.
| > I say : I don''''t even know, I don''''t
| > know nothing.