Tom’s Tutorials For Excel: Finding the First and Last Days of the Week and Month
Here are formulas to return various dates of first and last days of a given month.
First day’s date of that month: =DATE(YEAR(B1),MONTH(B1),1)
Last day’s date of that month: =DATE(YEAR(B1),MONTH(B1)+1,0)
First Monday date of that month:
=DATE(YEAR(B1),MONTH(B1),8)-WEEKDAY(DATE(YEAR(B1),MONTH(B1),6))
Last Friday date of that month:
=DATE(YEAR(B1),MONTH(B1)+1,1)-WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,1)-6)
First day of the month, 3 months later: =DATE(YEAR(B1),MONTH(B1)+3,1)
Last day of the month, 3 months later: =DATE(YEAR(B1),MONTH(B1)+4,0)
Here is shorter formula (one function call instead of three) to get the first day of the month…
=B1-DAY(B1)+1