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 do this?

Please help



Digital2k

Hi,
This is based on previous formulae posted by Ron Rosenfield:

To find the first day i.e. Monday, Tuesday etc the general formula is (for
date in A1) :

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-DOW)

where DOW is day of week (1=Sun, 2=Mon, etc)

For Wedneday (day=4) the formula becomes:

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-4)

OR

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+4)

and the third Wednesday becomes

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+4)+14

OR

=A1-DAY(A1-WEEKDAY(A1-DAY(A1)+4)+22


Using the above, the third Wednesday of the next month is:

=DATE(YEAR(A1),MONTH(A1)+1,1)-DAY(DATE(YEAR(A1),MONTH(A1)+1,1))-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-DAY(DATE(YEAR(A1),MONTH(A1)+1,1))+4)+22

i.e repeat for month+1

Hopefully you can use to apply to your If test - something like .....

=if(paydatethird_Wed_Date,next_month_third_Wed_Da te,paydate)


HTH

"Digital2k" wrote:

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 do this?

Please help



Digital2k