Excel - finding particular day

We'll concentrate on finding the date of the first Thursday; you can add 7, 14, or 21 to the results to get the second, third, or fourth Thursday. Let's suppose the month (a number from 1 to 12) is stored in cell A2 and the year is in B2. This formula will yield the date of the first Thursday: =DATE(B2,A2,1)+MOD (12-WEEKDAY(DATE(B2,A2,1)),7). We'll break it down to show why it works.

The formula DATE(B2,A2,1), which appears twice in the formula below, returns the date of the first day of the specified month. Passing that result to the WEEKDAY function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week for that date. Knowing the day of the week for the first of the month, you can calculate the date of the first Thursday. The day-number for Thursday is 5, but you can't just subtract the day-number for the first of the month from 5. (That would yield a negative number if the first fell on a Friday or Saturday). Instead, subtract from Thursday plus a week (12) and pass the result to the MOD function. The MOD function returns the remainder resulting from dividing its first argument by its second, so the result will always be from 0 to 6. Adding this number of days to the first day of the month yields the date for the first Thursday. Of course, you can easily modify this function to find the first Monday or another day of the week.