Get the First Monday of a Given Year

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 in excel. How to determine the first Monday in a given year date with a formula in excel. How to find and get the date of the first Monday of a given year date in excel.

Get the First Monday of a Given Year


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))

get first monday of the year1

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)

get first monday of the year2

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

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar