Calculating the First Tuesday

@Peter
I think it's an execellent idea. It makes the formula easier to use. And it appropriate you can still use ± n days.

Willy

Thanks for the new function and yes it is easier to understand. I will view Nick Rothstein and your comments after posting this.

I took the liberty of adding another variable to the Firstday function that I think makes it less likely to enter an error. What do you think?

Function FirstDay(Y As Integer, M As Integer, D As Integer, _
Optional wk2Add As Integer = 0) As Date
'Willy Vanhaelen
FirstDay = Application.Floor(DateSerial(Y, M, 7 - D), 7) + D + 7 * wk2Add
End Function

(see Figure 1 below)

@Peter
Thanks for the compliment. While trying to answer your question I did some testing and realised that in this case the Evaluate method is not the best choice.
The result is an even shorter version of the UDF (and easier to understand):

Function FirstDay(Y As Integer, M As Integer, D As Integer) As Date
FirstDay = Application.Floor(DateSerial(Y, M, 7 - D), 7) + D
End Function

The Evaluate method is a good choice though to implement Excel's array formulas in VBA to avoid the use of a Loop resulting in much faster macros. If you are interested you can read the comments of Rick Rothstein and myself in this tip:
https://excelribbon.tips.net/T010768_Shortening_ZIP_Codes.html
It's a fact that that the Evaluate method is the most poorly commented item in Excel's Help. If you want more, simple Google for "vba evaluate".

BTW: there is an error in row 4 of my picture: Third Tuesday of July should be Fourth Tuesday (or +21 in the formula must by +14 to get the third).

Willy

Excellent stuff as usual! The UDF is definitely one for the Personal workbook.

Can you point me to to a good write-up on the Evaluate function? Thanks.

Here is a one-liner User Defined Function that does it all:

Function FirstDay(Y As Integer, M As Integer, D As Integer) As Date
FirstDay = Evaluate("FLOOR(DATE(" & Y & "," & M & "," & 7 - D & "),7)+" & D)
End Function

Syntax: =FirstDay(year,month,weekday)
weekday: 1 for Sunday ... 7 for Saturday

If you need to use this often then place the macro in a module of your Personal workbook and it will be available in any workbook you open.
Here are some examples:

(see Figure 1 below)

@Peter Atherton
Here is a much shorter formula to do the job:

=FLOOR(DATE(B2,B1,7-B4),7)+B4

(see Figure 1 below)

Ignore the last post.

The following returns the first date for any workday.

=DATE(B2,B1,1)+ABS(7-WEEKDAY(DATE(B2,B1,1)+7-B4))

(see Figure 1 below)

Willy

a variation on your smashing formula

=CEILING(DATE($B$2,$B$1,1+Rwkday),7)-(1+Rwkday) to find th first occurrunce

and
=(CEILING(DATE($B$2,$B$1,1+Rwkday),7)-(1+Rwkday)+7*(WkNumber-1)) to get the nth occurence

(see Figure 1 below)

@Erik
If you use my formula you get the same result:
=CEILING(DATE(B2,B1,5),7)-4
but it is half the size :-).

If you input the month and year of interest in two cells, one short formula will tell you the date of the first Tuesday of that month.
For example:
• input your month (1-12) in cell B1
• input the four-digit year in cell B2
• use this formula: =DATE(B2,B1,1)+7-WEEKDAY(DATE(B2,B1,1),13)
Be sure the formula cell is formatted as a date so the result is legible.

Here is probably the shortest possible formula for the first approuch:

=CEILING(DATE(A2,A1,5),7)-4

As for the macro, here is a one-liner:

Function FirstTuesday(M As Integer, Y As Integer) As Date
FirstTuesday = Evaluate("CEILING(DATE(" & Y & " ," & M & ",5),7)-4")
End Function