How can I create a formla using date ranges

How can I create a formula that will do this: = IF cell A2 ="m" and if cell
D2 is between 5/19/06 and 6/20/06 than cell E2 ="June" + if A2 = "m" and D2
is between 6/21/06 and 7/18/06 than E2 = "July" and so on?
This is the formula I used:
=AND(A2="m")*IF(D2=38854:38888,"June")+AND(A2="m")*IF(D2=38889:38916,"July")
I either get an #Value!, or some other error. Please help!

If June / July is your only concern, then:

=if( A2 <> "m", ___whatever happens if A2 is not m____, if(
and(D2>38854,D2<=388888), "June", "July"))

Sounds like you want everything before the 19th in one month and
everything after in the next so:

=if( A2 <> "m", ___whatever happens if A2 is not m____, if(
day(D2)<19, month(D2), month(d2)+1))



Digital2k wrote:
> How can I create a formula that will do this: = IF cell A2 ="m" and if cell
> D2 is between 5/19/06 and 6/20/06 than cell E2 ="June" + if A2 = "m" and D2
> is between 6/21/06 and 7/18/06 than E2 = "July" and so on?
> This is the formula I used:
> =AND(A2="m")*IF(D2=38854:38888,"June")+AND(A2="m")*IF(D2=38889:38916,"July")
> I either get an #Value!, or some other error. Please help!

Nel post news:[email protected]
*Digital2k* ha scritto:

> How can I create a formula that will do this: = IF cell A2 ="m" and
> if cell D2 is between 5/19/06 and 6/20/06 than cell E2 ="June" + if
> A2 = "m" and D2 is between 6/21/06 and 7/18/06 than E2 = "July" and
> so on? This is the formula I used:
> =AND(A2="m")*IF(D2=38854:38888,"June")+AND(A2="m")*IF(D2=38889:38916,"July")
> I either get an #Value!, or some other error. Please help!


Maybe it should be better what are your needs, why do you need a such
formula, so we can help you better.

Help us to help you... :-)

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

How can I create a formula that will do this: = IF cell A2 ="m" and if cell
D2 is between 5/19/06 and 6/20/06 than cell E2 ="June" + if A2 = "m" and D2
is between 6/21/06 and 7/18/06 than E2 = "July" and so on?
This is the formula I used:
=AND(A2="m")*IF(D2=38854:38888,"June")+AND(A2="m") *IF(D2=38889:38916,"July")
I either get an #Value!, or some other error. Please help!

if((and(a2="m",d2>=38854,d2<=38888),"June",if(and(a2="m",d2>=38889,d2<=38916),"July","unknown"))

but a vlookup could be easier if you are going to expand to other months
=if(a2="m",vlookup(d2,a1:b10,2),"unknown")

where column a has the start dates of each of your ranges and b the corresponding month

Regards

Dav

I'm in the Insurance business and when I sell a product I have an expected
date that I think it will get approved and get paid. One of the products,
product "M" must get paid before the third Wednesday of the month if not
then it will go the following month.

I want to create a spreadsheet that will automatically make the adjustment
by changing the month paid if the product is product "m" and the expected
date paid is after the third Wednesday of the month. How can I create a
formula that will do this? please help and thank you in advance.



"Franz Verga" <[email protected]> wrote in message
news:[email protected]...
> Nel post news:[email protected]
> *Digital2k* ha scritto:
>
>> How can I create a formula that will do this: = IF cell A2 ="m" and
>> if cell D2 is between 5/19/06 and 6/20/06 than cell E2 ="June" + if
>> A2 = "m" and D2 is between 6/21/06 and 7/18/06 than E2 = "July" and
>> so on? This is the formula I used:
>> =AND(A2="m")*IF(D2=38854:38888,"June")+AND(A2="m")*IF(D2=38889:38916,"July")
>> I either get an #Value!, or some other error. Please help!
>
>
> Maybe it should be better what are your needs, why do you need a such
> formula, so we can help you better.
>
> Help us to help you... :-)
>
> --
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>

Nel post news:[email protected]
*Digital2k* ha scritto:

> I'm in the Insurance business and when I sell a product I have an
> expected date that I think it will get approved and get paid. One of
> the products, product "M" must get paid before the third Wednesday of
> the month if not then it will go the following month.
>
> I want to create a spreadsheet that will automatically make the
> adjustment by changing the month paid if the product is product "m"
> and the expected date paid is after the third Wednesday of the month.
> How can I create a formula that will do this? please help and thank
> you in advance.

Maybe I found a starting point:

=IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30))

where A30 is the cell in which you have your date and B30 is the cell with
product.

The formula returns the number of the month (6 for June, 7 for July, and so
on), you can format the cell to display the name by menu Format, Cells,
select the tab Number, choose Custum under Category, type mmm or mmmm if you
want the short or the long name of the month.

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Nel post news:[email protected]
*Franz Verga* ha scritto:

> Nel post news:[email protected]
> *Digital2k* ha scritto:
>
>> I'm in the Insurance business and when I sell a product I have an
>> expected date that I think it will get approved and get paid. One of
>> the products, product "M" must get paid before the third Wednesday of
>> the month if not then it will go the following month.
>>
>> I want to create a spreadsheet that will automatically make the
>> adjustment by changing the month paid if the product is product "m"
>> and the expected date paid is after the third Wednesday of the month.
>> How can I create a formula that will do this? please help and thank
>> you in advance.
>
> Maybe I found a starting point:
>
> =IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30))
>
> where A30 is the cell in which you have your date and B30 is the cell
> with product.
>
> The formula returns the number of the month (6 for June, 7 for July,
> and so on), you can format the cell to display the name by menu
> Format, Cells, select the tab Number, choose Custum under Category,
> type mmm or mmmm if you want the short or the long name of the month.


Just a little correction...

To have just the name of month you need to switch to this formula:

=DATE(YEAR(A30),IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30)),1)

and format as I wrote above.

If, instead you want to use this month's number to construct a new date, use
this one:

=DATE(YEAR(A31),IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30)),DAY(A31))

where, maybe, in A31 you can have another date...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Hello,

=IF(A1="m",TEXT(DATE(YEAR(D1),MONTH(D1)+1,DAY(D1)-20),"MMM");"something
else")

HTH,
Bernd

Nel post news:%[email protected]
*Franz Verga* ha scritto:

> Nel post news:[email protected]
> *Franz Verga* ha scritto:
>
>> Nel post news:[email protected]
>> *Digital2k* ha scritto:
>>
>>> I'm in the Insurance business and when I sell a product I have an
>>> expected date that I think it will get approved and get paid. One of
>>> the products, product "M" must get paid before the third Wednesday
>>> of the month if not then it will go the following month.
>>>
>>> I want to create a spreadsheet that will automatically make the
>>> adjustment by changing the month paid if the product is product "m"
>>> and the expected date paid is after the third Wednesday of the
>>> month. How can I create a formula that will do this? please help
>>> and thank you in advance.
>>
>> Maybe I found a starting point:
>>
>> =IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30))
>>
>> where A30 is the cell in which you have your date and B30 is the cell
>> with product.
>>
>> The formula returns the number of the month (6 for June, 7 for July,
>> and so on), you can format the cell to display the name by menu
>> Format, Cells, select the tab Number, choose Custum under Category,
>> type mmm or mmmm if you want the short or the long name of the month.
>
>
> Just a little correction...
>
> To have just the name of month you need to switch to this formula:
>
> =DATE(YEAR(A30),IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30)),1)
>
> and format as I wrote above.
>
> If, instead you want to use this month's number to construct a new
> date, use this one:
>
> =DATE(YEAR(A31),IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30)),DAY(A31))
>
> where, maybe, in A31 you can have another date...

I think I was too sleepy...

This *do* the job:

=IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>=3,B2="M"),MONTH(A2)+1,MONTH(A2))


Here in A2 you have the date to test and in B2 the product.

As above this formula returns just the number of a month, so if you want to
see it in plain text, you have to trasform it in a date and the format
properly, so use:

=DATE(DATE(YEAR(A2),IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>=3,B2="M"),MONTH(A2)+1,MONTH(A2)),1)

or

=DATE(DATE(YEAR(C2),IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>=3,B2="M"),MONTH(A2)+1,MONTH(A2)),DAY(C2))

if you want just the name of the month or a new date (in C2 you can have a
date from which you can take the year and/or the day)

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Thank you very much for this formula. It works perfect for June. But as I
use August, September and later, It does not change the month on the third
Wednesday of the month. This is the closes as anyone has come to making this
happen. if anyone can help me I would greatly appreciated.

Once again when I sell a product I have an
>>> expected date that I think it will get approved and get paid. One of
>>> the products, product "M" must get paid before the third Wednesday
>>> of the month if not then it will go the following month.
>>>
>>> I want to create a spreadsheet that will automatically make the
>>> adjustment by changing the month paid if the product is product "m"
>>> and the expected date paid is after the third Wednesday of the
>>> month. How can I create a formula that will do this? please help
>>> and thank you in advance.

<[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> =IF(A1="m",TEXT(DATE(YEAR(D1),MONTH(D1)+1,DAY(D1)-20),"MMM");"something
> else")
>
> HTH,
> Bernd
>

Thank you very much for your effort. I really appreciate your time. it was
close but it did not quite work foe me.
This formula was very close except it only works for the month of June:
=IF(A1="m",TEXT(DATE(YEAR(D1),MONTH(D1)+1,DAY(D1)-20),"MMM");"something
> else").
Maybe you can modify it. Thank so much.
Digital2k
"Franz Verga" <[email protected]> wrote in message
news:[email protected]...
> Nel post news:%[email protected]
> *Franz Verga* ha scritto:
>
>> Nel post news:[email protected]
>> *Franz Verga* ha scritto:
>>
>>> Nel post news:[email protected]
>>> *Digital2k* ha scritto:
>>>
>>>> I'm in the Insurance business and when I sell a product I have an
>>>> expected date that I think it will get approved and get paid. One of
>>>> the products, product "M" must get paid before the third Wednesday
>>>> of the month if not then it will go the following month.
>>>>
>>>> I want to create a spreadsheet that will automatically make the
>>>> adjustment by changing the month paid if the product is product "m"
>>>> and the expected date paid is after the third Wednesday of the
>>>> month. How can I create a formula that will do this? please help
>>>> and thank you in advance.
>>>
>>> Maybe I found a starting point:
>>>
>>> =IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30))
>>>
>>> where A30 is the cell in which you have your date and B30 is the cell
>>> with product.
>>>
>>> The formula returns the number of the month (6 for June, 7 for July,
>>> and so on), you can format the cell to display the name by menu
>>> Format, Cells, select the tab Number, choose Custum under Category,
>>> type mmm or mmmm if you want the short or the long name of the month.
>>
>>
>> Just a little correction...
>>
>> To have just the name of month you need to switch to this formula:
>>
>> =DATE(YEAR(A30),IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30)),1)
>>
>> and format as I wrote above.
>>
>> If, instead you want to use this month's number to construct a new
>> date, use this one:
>>
>> =DATE(YEAR(A31),IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30)),DAY(A31))
>>
>> where, maybe, in A31 you can have another date...
>
> I think I was too sleepy...
>
> This *do* the job:
>
> =IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>=3,B2="M"),MONTH(A2)+1,MONTH(A2))
>
>
> Here in A2 you have the date to test and in B2 the product.
>
> As above this formula returns just the number of a month, so if you want
> to see it in plain text, you have to trasform it in a date and the format
> properly, so use:
>
> =DATE(DATE(YEAR(A2),IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>=3,B2="M"),MONTH(A2)+1,MONTH(A2)),1)
>
> or
>
> =DATE(DATE(YEAR(C2),IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>=3,B2="M"),MONTH(A2)+1,MONTH(A2)),DAY(C2))
>
> if you want just the name of the month or a new date (in C2 you can have a
> date from which you can take the year and/or the day)
>
> --
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>

I think this sould work:

=IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>=3,B2="M"),MONTH(A2)+1,MONTH(A2))

Maybe it depends on how you consider the day that falls exactly on 3rd
Wednesday, maybe this is best suited to your needs:

=IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>3,B2="M"),MONTH(A2)+1,MONTH(A2))


Digital2k wrote:
> Thank you very much for your effort. I really appreciate your time.
> it was close but it did not quite work foe me.
> This formula was very close except it only works for the month of
> June:
> =IF(A1="m",TEXT(DATE(YEAR(D1),MONTH(D1)+1,DAY(D1)-20),"MMM");"something
>> else").
> Maybe you can modify it. Thank so much.
> Digital2k
> "Franz Verga" <[email protected]> wrote in message
> news:[email protected]...
>> Nel post news:%[email protected]
>> *Franz Verga* ha scritto:
>>
>>> Nel post news:[email protected]
>>> *Franz Verga* ha scritto:
>>>
>>>> Nel post news:[email protected]
>>>> *Digital2k* ha scritto:
>>>>
>>>>> I'm in the Insurance business and when I sell a product I have an
>>>>> expected date that I think it will get approved and get paid. One
>>>>> of the products, product "M" must get paid before the third
>>>>> Wednesday of the month if not then it will go the following month.
>>>>>
>>>>> I want to create a spreadsheet that will automatically make the
>>>>> adjustment by changing the month paid if the product is product
>>>>> "m" and the expected date paid is after the third Wednesday of the
>>>>> month. How can I create a formula that will do this? please help
>>>>> and thank you in advance.
>>>>
>>>> Maybe I found a starting point:
>>>>
>>>> =IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30))
>>>>
>>>> where A30 is the cell in which you have your date and B30 is the
>>>> cell with product.
>>>>
>>>> The formula returns the number of the month (6 for June, 7 for
>>>> July, and so on), you can format the cell to display the name by
>>>> menu Format, Cells, select the tab Number, choose Custum under
>>>> Category, type mmm or mmmm if you want the short or the long name
>>>> of the month.
>>>
>>>
>>> Just a little correction...
>>>
>>> To have just the name of month you need to switch to this formula:
>>>
>>> =DATE(YEAR(A30),IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30)),1)
>>>
>>> and format as I wrote above.
>>>
>>> If, instead you want to use this month's number to construct a new
>>> date, use this one:
>>>
>>> =DATE(YEAR(A31),IF(AND(DAY(A30)>=15,DAY(A30)<=21,B30="M"),MONTH(A30)+1,MONTH(A30)),DAY(A31))
>>>
>>> where, maybe, in A31 you can have another date...
>>
>> I think I was too sleepy...
>>
>> This *do* the job:
>>
>> =IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>=3,B2="M"),MONTH(A2)+1,MONTH(A2))
>>
>>
>> Here in A2 you have the date to test and in B2 the product.
>>
>> As above this formula returns just the number of a month, so if you
>> want to see it in plain text, you have to trasform it in a date and
>> the format properly, so use:
>>
>> =DATE(DATE(YEAR(A2),IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>=3,B2="M"),MONTH(A2)+1,MONTH(A2)),1)
>>
>> or
>>
>> =DATE(DATE(YEAR(C2),IF(AND(INT((A2-4)/7)-INT((DATE(YEAR(A2),MONTH(A2),1)-4-1)/7)>=3,B2="M"),MONTH(A2)+1,MONTH(A2)),DAY(C2))
>>
>> if you want just the name of the month or a new date (in C2 you can
>> have a date from which you can take the year and/or the day)
>>
>> --
>> Hope I helped you.
>>
>> Thanks in advance for your feedback.
>>
>> Ciao
>>
>> Franz Verga from Italy

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Originally Posted by Digital2k

How can I create a formula that will do this: = IF cell A2 ="m" and if cell
D2 is between 5/19/06 and 6/20/06 than cell E2 ="June" + if A2 = "m" and D2
is between 6/21/06 and 7/18/06 than E2 = "July" and so on?
This is the formula I used:
=AND(A2="m")*IF(D2=38854:38888,"June")+AND(A2="m")*IF(D2=38889:38916,"July")
I either get an #Value!, or some other error. Please help!

Try this formula in E2

=IF(A2="M",TEXT(DATE(0,MONTH(D2)+(D2>=DATE(YEAR(D2),MONTH(D2),22)-WEEKDAY(DATE(YEAR(D2),MONTH(D2),4))),1),"mmmm"),"")

hi, I setup an very long formula to calculate it, see whether is good for
you.
=IF(A2="m",IF(D2<=EOMONTH(D2,-1)+21-IF(WEEKDAY(EOMONTH(D2,-1)+19,2)>=3,(WEEKDAY(EOMONTH(D2,-1)+21,2)-2),(WEEKDAY(EOMONTH(D2,-1)+21,2)+5)),MONTH(D2),MONTH(D2)+1),"UNKNOWN")


"Digital2k" <[email protected]> wrote in message
news:[email protected]...
> Thank you very much for this formula. It works perfect for June. But as I
> use August, September and later, It does not change the month on the third
> Wednesday of the month. This is the closes as anyone has come to making
> this happen. if anyone can help me I would greatly appreciated.
>
> Once again when I sell a product I have an
>>>> expected date that I think it will get approved and get paid. One of
>>>> the products, product "M" must get paid before the third Wednesday
>>>> of the month if not then it will go the following month.
>>>>
>>>> I want to create a spreadsheet that will automatically make the
>>>> adjustment by changing the month paid if the product is product "m"
>>>> and the expected date paid is after the third Wednesday of the
>>>> month. How can I create a formula that will do this? please help
>>>> and thank you in advance.
>
> <[email protected]> wrote in message
> news:[email protected]...
>> Hello,
>>
>> =IF(A1="m",TEXT(DATE(YEAR(D1),MONTH(D1)+1,DAY(D1)-20),"MMM");"something
>> else")
>>
>> HTH,
>> Bernd
>>
>
>

You are the MAN!!!! This works Great! Thank you oh so very much!
There were a lot of people who were close, but you hit it on the head.
Thanks to all who tried, I really appreciate all tour efforts.
Digital2k

"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...
>
> Digital2k Wrote:
>> How can I create a formula that will do this: = IF cell A2 ="m" and if
>> cell
>> D2 is between 5/19/06 and 6/20/06 than cell E2 ="June" + if A2 = "m"
>> and D2
>> is between 6/21/06 and 7/18/06 than E2 = "July" and so on?
>> This is the formula I used:
>> =AND(A2="m")*IF(D2=38854:38888,"June")+AND(A2="m")*IF(D2=38889:38916,"July")
>> I either get an #Value!, or some other error. Please help!
>
> Try this formula in E2
>
> =IF(A2="M",TEXT(DATE(0,MONTH(D2)+(D2>=DATE(YEAR(D2),MONTH(D2),22)-WEEKDAY(DATE(YEAR(D2),MONTH(D2),4))),1),"mmmm"),"")
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=554930
>

Hello Group,

The formula given to me by daddylonglegs works fine but I want to add to the
equation:

=IF(A2="M",TEXT(DATE(0,MONTH(D2)+(D2>=DATE(YEAR(D2),MONTH(D2),22)-WEEKDAY(DATE(YEAR(D2),MONTH(D2),4))),1),"mmmm"),"")I want to also add:IF(A2="N",D2,) Cell D2 has a date. So, if cell A2="N" I want to use thedate in D2 and change it to "mmm"in cell E2. And finally, I want to add thesame formula only using "O".When I try to add to the top formula, I get #VALUE!Please help with this"daddylonglegs" <[email protected]>wrote in messagenews:[email protected]...>> Digital2k Wrote:>> How can I create a formula that will do this: = IF cell A2 ="m" and if>> cell>> D2 is between 5/19/06 and 6/20/06 than cell E2 ="June" + if A2 = "m">> and D2>> is between 6/21/06 and 7/18/06 than E2 = "July" and so on?>> This is the formula I used:>>=AND(A2="m")*IF(D2=38854:38888,"June")+AND(A2="m")*IF(D2=38889:38916,"July")>> I either get an #Value!, or some other error. Please help!>> Try this formula in E2>>=IF(A2="M",TEXT(DATE(0,MONTH(D2)+(D2>=DATE(YEAR(D2),MONTH(D2),22)-WEEKDAY(DATE(YEAR(D2),MONTH(D2),4))),1),"mmmm"),"")>>> --> daddylonglegs> ------------------------------------------------------------------------> daddylonglegs's Profile:http://www.excelforum.com/member.php?action=getinfo&userid=30486> View this thread: http://www.excelforum.com/showthread.php?threadid=554930>

Not sure I completely understand. If A2="M" formula works as before but if A2 = "N" or "O" you want D2's month (e.g. "June") in E2?

If so change formula to

=IF(OR(A2="N",A2="O"),TEXT(D2,"mmmm"),IF(A2="M",TEXT(DATE(0,MONTH(D2)+(D2>=DATE(YEAR(D2 ),MONTH(D2),22)-WEEKDAY(DATE(YEAR(D2),MONTH(D2),4))),1),"mmmm"),"" ))

You are truly the man!
This helps.
Thank you
Digital2k




"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...
>
> Not sure I completely understand. If A2="M" formula works as before but
> if A2 = "N" or "O" you want D2's month (e.g. "June") in E2?
>
> If so change formula to
>
> =IF(OR(A2="N",A2="O"),TEXT(D2,"mmmm"),IF(A2="M",TEXT(DATE(0,MONTH(D2)+(D2>=DATE(YEAR(D2
> ),MONTH(D2),22)-WEEKDAY(DATE(YEAR(D2),MONTH(D2),4))),1),"mmmm"),"" ))
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=554930
>