VideoPhoto

25 April, 2021

Get DATES for the first 7 days of any Month of any Year

What was the date on that first Wednesday of December, 1985? Was is the 3rd, or 4th of December? - you wonder.

Well, that kind of questions can be answered in Excel with a formula. It's not a short or simple one, but it works and can be used (with simple modification) to determine the date of any first weekday of any month of any year (from 1900 up). The figure below illustrates the result:

In order to produce such quite useful table follow these steps:

  • Enter "Date of the 1st …" in cell A1
  • Enter the following Formulas in cells A2 to A8:
="Monday in "&TEXT(B1,"mmm yyyy"
="Tuesday in "&TEXT(B2,"mmm yyyy")
="Wednesday in "&TEXT(B3,"mmm yyyy")
="Thursday in "&TEXT(B4,"mmm yyyy")
="Friday in "&TEXT(B5,"mmm yyyy")
="Saturday in "&TEXT(B6,"mmm yyyy")
="Sunday in "&TEXT(B7,"mmm yyyy")

  • Enter the following formulas in cells B2 to B8:

=IF(DAY(DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)-2,7)+7)<>8,DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)-2,7)+7,DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)-2,7))

=IF(DAY(DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)-3,7)+7)<>8,DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)-3,7)+7,DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)-3,7))

=IF(DAY(DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)-4,7)+7)<>8,DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)-4,7)+7,DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)-4,7))

=IF(DAY(DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)+2,7)+7)<>8,DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)+2,7)+7,DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)+2,7))

=IF(DAY(DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)+1,7)+7)<>8,DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)+1,7)+7,DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)+1,7))

=IF(DAY(DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)+0,7)+7)<>8,DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)+0,7)+7,DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)+0,7))

=IF(DAY(DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)-1,7)+7)<>8,DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)-1,7)+7,DATE(YEAR($B$1),MONTH($B$1),1)-MOD(DATE(YEAR($B$1),MONTH($B$1),1)-1,7))

  • Now, enter any month and year, as your need may be, in cell B1 (e.g. "Dec 2021").  

Cells B2:B8 will display the dates you're looking for.

 

No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.