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.

Table of Contents

## 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])…

## Leave a Reply

You must be logged in to post a comment.