Formula to display all monday's in a month

[raymondm]

I am trying to create a formula which will list all working day mondays in a calendar month (dd-mmm-yy)

I need to prepare a reporting schedule and want to display the dates when reports are due. There is a report that goes out every monday of the month.

can you help?

 

[SteveO59L]

What happens when Monday isnt a working day ?

 

[raymondm]

When Monday isnt a working day, then, the report will need to go out on the next working day

 

[MarkVMcCullagh]

Month of Mondays

Quick and Dirty Solution

In Cell A1 plug your month in date format

In Cell b1 type
=IF(WEEKDAY(EOMONTH(A1,-1)+1)=2,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+10-WEEKDAY(EOMONTH(A1,-1)+1))

In Cell c1 type
=B1+7

In Cell d1 type
=C1+7

In Cell E1 type
=D1+7

In Cell F1 type
=IF(EOMONTH(E1+7,0)-EOMONTH(A1,0)=0,E1+7,"")

 

[Domenic]

Assuming that A2 contains the month of interest, and G2:G10 contains a list of holidays, try the following...

1) Define the following...

Insert > Name > Define

Name: Array

Refers to:

=ROW(INDIRECT(DATE(YEAR($A$2),MONTH($A$2),1)&":"&DATE(YEAR($A$2),MONTH($A$2)+1,0)))

Note that if the date in A2 will always be the first day of the month and year of interest, the following would suffice...

=ROW(INDIRECT($A$2&":"&DATE(YEAR($A$2),MONTH($A$2)+1,0)))

Click Ok

2) Then try the following formulas...

B2:

=SUMPRODUCT(--(WEEKDAY(Array,2)=1))

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(C$2:C2)<=$B$2,SMALL(IF(WEEKDAY(Array,2)=1,(Array+ISNUMBER(MATCH(Array,$G$2:$G$10,0)))),ROWS(C$2:C2)),"")

Hope this helps!

 

[raymondm]

Thanks!!

Thanks a ton guys!! The first one was really simple but doesnt take into account bank holidays.....

 

[Domenic]

Re: Thanks!!

Thanks a ton guys!! The first one was really simple but doesnt take into account bank holidays.....

Assuming that A2 contains the first day of the month and year of interest, and G2:G10 contains the list of holidays...

For the first Monday of the month:

=(A2-IF(WEEKDAY(A2,3)>0,WEEKDAY(A2,3)-7,0))+ISNUMBER(MATCH(A2-IF(WEEKDAY(A2,3)>0,WEEKDAY(A2,3)-7,0),G2:G10,0))

For the second Monday of the month:

=(A2-IF(WEEKDAY(A2,3)>0,WEEKDAY(A2,3)-14,-7))+ISNUMBER(MATCH(A2-IF(WEEKDAY(A2,3)>0,WEEKDAY(A2,3)-14,-7),G2:G10,0))

For the third Monday of the month:

=(A2-IF(WEEKDAY(A2,3)>0,WEEKDAY(A2,3)-21,-14))+ISNUMBER(MATCH(A2-IF(WEEKDAY(A2,3)>0,WEEKDAY(A2,3)-21,-14),G2:G10,0))

For the fourth Monday of the month:

=(A2-IF(WEEKDAY(A2,3)>0,WEEKDAY(A2,3)-28,-21))+ISNUMBER(MATCH(A2-IF(WEEKDAY(A2,3)>0,WEEKDAY(A2,3)-28,-21),G2:G10,0))

For the fifth Monday of the month:

=(A2-IF(WEEKDAY(A2,3)>0,WEEKDAY(A2,3)-35,-28))+ISNUMBER(MATCH(A2-IF(WEEKDAY(A2,3)>0,WEEKDAY(A2,3)-35,-28),G2:G10,0))

Note that not all months will have a fifth Monday.  When this is the case, the next Monday in the following month will be returned.  In this case, conditional formatting can be used to hide the result.  Post back if you need help with this.

Hope this helps!

 

[raymondm]

Thanks..........

Thank you! This one is much simpler to understand.

 

[barry houdini]

Using Domenic's suggested setup, i.e. A2 contains the first of the relevant month and G2:G10 contains holidays, you could also try this, using WORKDAY function from Analysis ToolPak.

In A3 copied down to A7

=WORKDAY(A$2+ROWS(A$3:A3)*7-1-WEEKDAY(A$2+5),1,G$2:G$10)

To only show dates in the relevant month change to

=IF(A2="","",IF(WORKDAY(A$2+ROWS(A$3:A3)*7-1-WEEKDAY(A$2+5),1,G$2:G$10)>EOMONTH(A2,0),"",WORKDAY(A$2+ROWS(A$3:A3)*7-1-WEEKDAY(A$2+5),1,G$2:G$10)))