Nel post news:%23Tl0q7zlGHA.3924@TK2MSFTNGP05.phx.gbl
*Franz Verga* ha scritto:

> Nel post news:Ox6af0zlGHA.3980@TK2MSFTNGP02.phx.gbl
> *Franz Verga* ha scritto:
>
>> Nel post news:qNWdneIkx_FMBAHZnZ2dnUVZ_qCdnZ2d@adelphia.com
>> *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