Hi,

I need three formulae that use the current date to give the dates of the
last three 'Patch Tuesdays', i.e. the second Tuesday of the month.
So for today (15th Nov) the relevant dates are 13th Nov, 9th Oct, 11th Sep,
but a week ago the results would have been 9th Oct, 11th Sep, 14th Aug.

Anyone?

TIA
Pete

This will return the second Tuesday of the same month as today's date

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+14-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)+4)


so you can compare to see if today is greater than or equal to the 2nd
Tuesday

=IF(TODAY()=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+ 14-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)+4),DA TE(YEAR(TODAY()),MONTH(TODAY()),1)+14-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)+4),DA TE(YEAR(TODAY()),MONTH(TODAY())-1,1)+14-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+4))


that will give you 11/13/07 for today's date and 10/09/07 if today was one
week ago

so if you put this formula in B1 and you want the previous month's 2nd
Tuesday in B2 then use


=DATE(YEAR(B1),MONTH(B1)-1,1)+14-WEEKDAY(DATE(YEAR(B1),MONTH(B1)-1,1)+4)


copy down the last formula one row and you will get the previous month's 2nd
Tuesday


--


Regards,


Peo Sjoblom


"PM" wrote in message
...
Hi,

I need three formulae that use the current date to give the dates of the
last three 'Patch Tuesdays', i.e. the second Tuesday of the month.
So for today (15th Nov) the relevant dates are 13th Nov, 9th Oct, 11th
Sep,
but a week ago the results would have been 9th Oct, 11th Sep, 14th Aug.

Anyone?

TIA
Pete

That's perfect Peo, thanks!

"Peo Sjoblom" wrote in message
...
This will return the second Tuesday of the same month as today's date


=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+14-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TO
DAY()),1)+4)


so you can compare to see if today is greater than or equal to the 2nd
Tuesday


=IF(TODAY()=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+ 14-WEEKDAY(DATE(YEAR(TODAY
()),MONTH(TODAY()),1)+4),DATE(YEAR(TODAY()),MONTH( TODAY()),1)+14-WEEKDAY(DAT
E(YEAR(TODAY()),MONTH(TODAY()),1)+4),DATE(YEAR(TOD AY()),MONTH(TODAY())-1,1)+
14-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+4))


that will give you 11/13/07 for today's date and 10/09/07 if today was one
week ago

so if you put this formula in B1 and you want the previous month's 2nd
Tuesday in B2 then use


=DATE(YEAR(B1),MONTH(B1)-1,1)+14-WEEKDAY(DATE(YEAR(B1),MONTH(B1)-1,1)+4)


copy down the last formula one row and you will get the previous month's
2nd
Tuesday

Thanks for the feedback


--


Regards,


Peo Sjoblom


"PM" wrote in message
...
That's perfect Peo, thanks!

"Peo Sjoblom" wrote in message
...
This will return the second Tuesday of the same month as today's date


=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+14-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TO
DAY()),1)+4)


so you can compare to see if today is greater than or equal to the 2nd
Tuesday


=IF(TODAY()=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+ 14-WEEKDAY(DATE(YEAR(TODAY
()),MONTH(TODAY()),1)+4),DATE(YEAR(TODAY()),MONTH( TODAY()),1)+14-WEEKDAY(DAT
E(YEAR(TODAY()),MONTH(TODAY()),1)+4),DATE(YEAR(TOD AY()),MONTH(TODAY())-1,1)+
14-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+4))


that will give you 11/13/07 for today's date and 10/09/07 if today was
one
week ago

so if you put this formula in B1 and you want the previous month's 2nd
Tuesday in B2 then use


=DATE(YEAR(B1),MONTH(B1)-1,1)+14-WEEKDAY(DATE(YEAR(B1),MONTH(B1)-1,1)+4)


copy down the last formula one row and you will get the previous month's
2nd
Tuesday

This will return the second Tuesday of the same month as today's date
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+14-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)+4)

Just for a variation, this uses EOMONTH().

=EOMONTH(TODAY(),-1)+MOD(14,WEEKDAY(EOMONTH(TODAY(),-1),2)+6)+8

--
Dana DeLouis


"Peo Sjoblom" wrote in message
...
This will return the second Tuesday of the same month as today's date

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+14-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)+4)


so you can compare to see if today is greater than or equal to the 2nd
Tuesday

=IF(TODAY()=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+ 14-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)+4),DA TE(YEAR(TODAY()),MONTH(TODAY()),1)+14-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)+4),DA TE(YEAR(TODAY()),MONTH(TODAY())-1,1)+14-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)+4))


that will give you 11/13/07 for today's date and 10/09/07 if today was one
week ago

so if you put this formula in B1 and you want the previous month's 2nd
Tuesday in B2 then use


=DATE(YEAR(B1),MONTH(B1)-1,1)+14-WEEKDAY(DATE(YEAR(B1),MONTH(B1)-1,1)+4)


copy down the last formula one row and you will get the previous month's
2nd Tuesday


--


Regards,


Peo Sjoblom


"PM" wrote in message
...
Hi,

I need three formulae that use the current date to give the dates of the
last three 'Patch Tuesdays', i.e. the second Tuesday of the month.
So for today (15th Nov) the relevant dates are 13th Nov, 9th Oct, 11th
Sep,
but a week ago the results would have been 9th Oct, 11th Sep, 14th Aug.

Anyone?

TIA
Pete

On Thu, 15 Nov 2007 15:09:15 -0000, "PM" wrote:

Hi,

I need three formulae that use the current date to give the dates of the
last three 'Patch Tuesdays', i.e. the second Tuesday of the month.
So for today (15th Nov) the relevant dates are 13th Nov, 9th Oct, 11th Sep,
but a week ago the results would have been 9th Oct, 11th Sep, 14th Aug.

Anyone?

TIA
Pete


Here's another way. The hard part is figuring out whether the most recent
"last" Patch Tuesday should be in this month or the previous month. So,
assuming your initial date is in A1:

Most recent "last Patch Tuesday"
B1:

=IF(DAY(A1-DAY(A1)+18-WEEKDAY(A1-DAY(A1)+15))<DAY(A1),
A1-DAY(A1)+18-WEEKDAY(A1-DAY(A1)+15),A1-DAY(A1)+18-WEEKDAY(
A1-DAY(A1)+15)-28-7*((DAY(A1-DAY(A1)+18-WEEKDAY(A1-DAY(A1)+15)-28)14)))

B2: Patch Tuesday previous to that in B1:

=B1-28-7*((DAY(B1-28)14))

Then fill down B2 as far as you need.
--ron

Three fine solutions. Thanks all for your input.