Tom’s Tutorials For Excel: Finding the First and Last Days of the Week and Month

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)

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
One comment on “Tom’s Tutorials For Excel: Finding the First and Last Days of the Week and Month
  1. Rick Rothstein says:

    Here is shorter formula (one function call instead of three) to get the first day of the month…

    =B1-DAY(B1)+1

Leave a Reply

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

*