Calculating Patch Tuesday

[PM]

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

 

[Peo Sjoblom]

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),DATE(YEAR(TODAY()),MONTH(TODAY()),1)+14-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)+4),DATE(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]

That's perfect Peo, thanks!

 

[Peo Sjoblom]

Thanks for the feedback


--


Regards,


Peo Sjoblom

 

[Dana DeLouis]

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

 

[Ron Rosenfeld]

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

 

[PM]

Three fine solutions. Thanks all for your input.