Note: The other languages of the website are Google-translated. Back to English

How to find and get the first Monday of a specific year in Excel?

In a worksheet, how could you calculate and get the first Monday of a year from a given date? This article, I will introduce some useful formulas for solving this task in Excel.

Calculate and get the first Monday of a given year with formulas

Calculate and get the first Monday of a given year with formulas

Please apply the following formulas to calculate the first Monday of the given year:

Enter this formula: =DATE(YEAR(A2),1,8)-WEEKDAY(DATE(YEAR(A2),1,6)) into a blank cell where you want to locate the result, and then drag the fill handle down to the cells you want to apply this formula, and the date of the first Monday from the given date has been displayed at once, see screenshot:


1. If you have a list of year number cells which not in date format, please apply this formula:

=DATE(A2,1,1)+CHOOSE(WEEKDAY(DATE(A2,1,1),2),0,6,5,4,3,2,1) to get the first Monday date based on the specific year, see screenshot:

2. In the above formulas, A2 is the cell contains the date or year that you want to get the first Monday from.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (3)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Really bummed and disappointed you didn't take the time to explain the formula so we can adapt to other needs.
This comment was minimized by the moderator on the site
Completely agree; also, the formula does not work. Here's the formula in action.8/5/2014 Thursday, January 2, 2014
8/5/2015 Friday, January 2, 2015
8/5/2016 Saturday, January 2, 2016
8/5/2017 Monday, January 2, 2017
This comment was minimized by the moderator on the site
I can't login, and my previous response to stormtrance was incorrect. The formula DOES work. I do wish, though, that there was an explanation so I could figure out how to get each Monday and Friday date of every month.
There are no comments posted here yet
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations