Re: How can I create a formla using date ranges

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <fra68ve@xxxxxxxxxx> wrote in message
news:Onwwia2lGHA.4596@xxxxxxxxxxxxxxxxxxxxxxx
Nel post news:%23Tl0q7zlGHA.3924@xxxxxxxxxxxxxxxxxxxx
*Franz Verga* ha scritto:

Nel post news:Ox6af0zlGHA.3980@xxxxxxxxxxxxxxxxxxxx
*Franz Verga* ha scritto:

Nel post news:qNWdneIkx_FMBAHZnZ2dnUVZ_qCdnZ2d@xxxxxxxxxxxx
*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


.

Relevant Pages

  • Re: How can I create a formla using date ranges
    ... *Franz Verga* ha scritto: ... product "M" must get paid before the third Wednesday ... where A30 is the cell in which you have your date and B30 is the cell ... Format, Cells, select the tab Number, choose Custum under Category, ...
    (microsoft.public.excel)
  • Re: How can I create a formla using date ranges
    ... *Franz Verga* ha scritto: ... product "M" must get paid before the third Wednesday ... Format, Cells, select the tab Number, choose Custum under Category, ...
    (microsoft.public.excel)
  • Re: Sorting data within the same row.
    ... fwd invoices paid and invoices raised in the month which are paid. ... Cell B1, same number format, colour fill - Yellow; ... The above cells are for format reference only. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: How can I create a formla using date ranges
    ... *Franz Verga* ha scritto: ... expected date that I think it will get approved and get paid. ... where A30 is the cell in which you have your date and B30 is the cell ... Franz Verga from Italy ...
    (microsoft.public.excel)
  • Re: How can I create a formla using date ranges
    ... *Digital2k* ha scritto: ... expected date that I think it will get approved and get paid. ... where A30 is the cell in which you have your date and B30 is the cell with ... on), you can format the cell to display the name by menu Format, Cells, ...
    (microsoft.public.excel)