This post will guide you how to calculate the thanksgiving data based on a specific year date using a formula or a User Defined Function with VBA Code in excel 2013/2016/2019/365. How do I get the thanksgiving date based on a specific years in excel.

1. Calculate the US Thanksgiving Date using a Formula

You can use a simple arithmetic to calculate the possible date range for the US Thanksgiving, it falls on the fourth Thursday in November. And you can create a formula based on the DATE function and the WEEKDAY function.

To Calculate the US Thanksgiving date, you can write down the following formula:

=DATE(B2,11,29)-WEEKDAY(DATE(B2,11,3))

Or

=DATE(C2,11,29)-WEEKDAY(DATE(C2,11,24))

You just need to type this formula in the formula box of cell C2, and press Enter in your keyboard. Then drag the AutoFill Handler over other cell to apply this formula.

2. Calculate Thanksgiving Date using a User Defined Function with VBA Code

If you want to calculate the Thanksgiving date using a user-defined function in Excel, you can follow these steps:

Step1: Press ALT + F11 to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, click Insert > Module.

Step3: In the Module window, paste the following code:

Function ThanksgivingDate_Excelhow(ByVal Year As Integer) As Date
    ThanksgivingDate_Excelhow = DateSerial(Year, 11, 29) - Weekday(DateSerial(Year, 11, 3))
End Function

Step4: Save the module and close the Visual Basic Editor.

Step5: enter the year you want to calculate the Thanksgiving date for in a cell, for example, cell A2.  In another cell, enter the formula:

=ThanksgivingDate_Excelhow(B2)

Step6: press Enter key, the cell will now display the Thanksgiving date for the year you entered in cell A2.

3. Video: Calculate Thanksgiving Date in Excel

This video shows how to calculate the Thanksgiving date in Excel using both the DATE function and a user-defined function with VBA code.

4. Related Functions

• Excel DATE function
  The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
• Excel WEEKDAY function
  The Excel WEEKDAY function returns a integer value representing the day fo the week for a given Excel date and the value is range from 1 to 7.The syntax of the WEEKDAY function is as below:=WEEKDAY (serial_number,[return_type])…