Re: How can I create a formla using date ranges

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

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


.

Relevant Pages

  • 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. ... cell with product. ... July, and so on), you can format the cell to display the name by ...
    (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 ... 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: ... 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: Another Qustion concerning dates
    ... *Digital2k* ha scritto: ... I want to know what I will get paid TODAY, ... 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)