This post will guide you how to get the first Monday of a given year or date in excel. How do I calculate the first Monday of a specific year using a formula in excel. How to determine the first Monday in a given year date with a formula using VBA Code in excel. How to find and get the date of the first Monday of a given year date in excel.

1. Get the First Monday of a Given Year Using Formula

To calculate the first Monday of the year or given any date, you can create a new complex formula based on the DATE function, the YEAR function and the WEEKDAY function.

Assuming that you have a date list in the range of cells B1:B4 in your current worksheet, and if you want to get the first Monday for the year date in each cell, and you can write down one formula as below:

=DATE(YEAR(B1),1,8)-WEEKDAY(DATE(YEAR(B1),1,6))

Note: The formula calculates the date of January 1st of the given year, and then adds the number of days needed to reach the next Monday (8 minus the weekday of January 1st).

Type this formula into the formula box of a blank cell such as: C1, then press Enter key in your keyboard, and drag the AutoFill Handler over other cells to apply this formula.

You can also use another formula based on the DATE function, the CHOOSE function and the WEEKDAY function to create the same result. Like this:

=DATE(B1,1,1)+CHOOSE(WEEKDAY(DATE(B1,1,1),2),0,6,5,4,3,2,1)

2. Get the First Monday of a Given Year Using a User-Defined Function (VBA)

You can also easily get the first Monday of a given year using User Defined function with VBA code in Excel. You just need to follow these detail steps:

Step1: press ALT + F11 shortcut keys to open Visual Basic for Application Editor(VBA) or navigating to the Developer tab and click on Visual Basic.

Step2:  click on Insert Menu, then select Module in VBA editor.

Step3: type the below VBA code in the Module window, save the VBA project by clicking on the Save button.

Function FirstMondayOfYear(ByVal year As Integer) As Date
    Dim dateCheck As Date
    dateCheck = DateSerial(year, 1, 1)
    Do While Weekday(dateCheck) <> vbMonday
        dateCheck = dateCheck + 1
    Loop
    FirstMondayOfYear = dateCheck
End Function

Step4: find one blank cell to type the below formula based on the custom VBA function. press Enter key. The cell should display the date of the first Monday of the given year

=FirstMondayOfYear(A1)

You need to replace the year value as you need that you want to find the first Monday.

3. Video: Get the First Monday of a Given Year

This tutorial video will explore two effective methods to find the first Monday of any given year in Excel.

4. SAMPLE FIlES

Below are sample files in Microsoft Excel that you can download for reference if you wish.

5. Related Functions

• 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])…
• 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 YEAR function
  The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
• Excel Choose Function
  The Excel CHOOSE function returns a value from a list of values. The CHOOSE function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the CHOOSE function is as below:=CHOOSE (index_num, value1,[value2],…)…