Need formula for date ranges Please & Thanks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Digital2k - 24 Jun 2006 03:09 GMT

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

Reply to this Message

Toppers - 24 Jun 2006 11:20 GMT

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(paydate>third_Wed_Date,next_month_third_Wed_Date,paydate)

HTH

> 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,
[quoted text clipped - 8 lines]
>
> Digital2k

Reply to this Message