Date Function
Posted by Scott Currie on March 29, 2001 7:24 AM
How can I reference every 2nd Tuesday of the month to a function?
Eg.
Col. B - I insert 3/13/01
Col. C - 4/10/01 should appear.
Any help?
Posted by mseyf on March 29, 2001 8:37 AM
Scott-
One way of doing it:
Function SecondTues(Date1 As Date) As Date
Dim NextMonth As Date
NextMonth = DateSerial(Year(Date1), Month(Date1) + 1, 1)
SecondTues = NextMonth + Choose(WeekDay(NextMonth), 9, 8, 7, 13, 12, 11, 10)
End Function
HTH
Mark
Posted by Mark W. on March 29, 2001 9:36 AM
Which Month?
Do you want the value in column C to always be the
2nd Tuesday of the month following the date in column
B?
Posted by Scott Currie on March 29, 2001 11:27 AM
Re: Which Month?
--->Yes.
Posted by mseyf on March 29, 2001 11:39 AM
Scott:
if you want a formula instead of a function, try:
=DATE(YEAR(B2),MONTH(B2)+1,1)+CHOOSE(WEEKDAY(DATE(YEAR(B2),MONTH(B2)+1,1)),9,8,7,13,12,11,10)
Posted by Mark W. on March 29, 2001 12:30 PM
Here's a variation of Mark's formula...
=EOMONTH(A1,0)+CHOOSE(WEEKDAY(EOMONTH(A1,0)),9,8,7,13,12,11,10)
The EOMONTH() function is only available after the
addition of the Analysis ToolPak add-in.
Posted by Mark W. on March 29, 2001 1:44 PM
"Bug" fix...
...had to go to 10/1/2006 to find the "bug", but here's
the fix: =EOMONTH(A1,0)+CHOOSE(WEEKDAY(EOMONTH(A1,0)),9,8,14,13,12,11,10) The EOMONTH() function is only available after the
