Calculating the first day of the week for the month

  • Thread starter Thread starter Justin
  • Start date Start date
J

Justin

I want to show the date of each of the Mondays in the
month based upon the month typed in cell A4.

Example

A4 will show September(whether typed by a user or from a
combo box).

Cell B1 will show the first Monday
Cell B2 will show the second Monday
Cell B3 will show the third Monday
Cell B4 will show the fourth Monday
Cell B5 will show the fifth Monday (example: 2004 has 4
moths that have 5 Mondays)

Thanks fo any help you can provide.
Justin
 
Cell A4 MUST contain a valid date (first day of the month) for this to work (ie September 1, 2003). So enter a valid date and
custom format it as "mmmm" which will show only the month while containing a valid date.

Then in cell B1 put
=IF(TEXT(A4,"ddd") = "Mon", A4,IF(TEXT(A4+1,"ddd") = "Mon", A4+1,IF(TEXT(A4+2,"ddd") = "Mon", A4+2,IF(TEXT(A4+3,"ddd") = "Mon",
A4+3,IF(TEXT(A4+4,"ddd") = "Mon",A4+ 4,IF(TEXT(A4+5,"ddd") = "Mon", A4+5,IF(TEXT(A4+6,"ddd") = "Mon", A4+6 )))))))

In cell B2 put
=IF(TEXT(B1,"mmmm") = TEXT(B1+7,"mmmm"),B1+7, "")
and drag down four cells.

Note: if values appear like 37837 then change the formatting to dates.

Dan E
 
Back
Top