1st monday

[Trevor3007]

hi,

is it viable to have a formula to show the 1st monday of a month as a date based on the date in a2?

For example

A2 B2 (1st monday of month)
1/10/18 1/10/18

8/10/18 1/10/18

5/11/18 5/11/18

12/11/18 5/11/18


MTIA & for your time today.
KR
Trevor3007

 

[Rick Rothstein]

is it viable to have a formula to show the 1st monday of a month as a date based on the date in a2?

For example

A2 B2 (1st monday of month)
1/10/18 1/10/18

8/10/18 1/10/18

5/11/18 5/11/18

12/11/18 5/11/18

Give this formula a try...

=DATE(YEAR(A2),MONTH(A2),8)-WEEKDAY(DATE(YEAR(A2),MONTH(A2),6))

 

[Eric W]

Try:

=8-WEEKDAY(EOMONTH(A2,-1),2)+EOMONTH(A2,-1)

 

[Rick Rothstein]

Try:

=8-WEEKDAY(EOMONTH(A2,-1),2)+EOMONTH(A2,-1)

I like this formula approach (less than half the function calls that my formula requires).

It looks like this formula approach can be generalized to find the Nth such-and-such day of the week as follows...

=1+7*Nth-WEEKDAY(EOMONTH(A2,-1),10+DoW)+EOMONTH(A2,-1)

where Nth would be the occurrence number (1 for first, 2 for second, etc.) for the DoW (day of week)(1 for Monday, 2 for Tuesday, etc.).

 

[Rick Rothstein]

I like this formula approach (less than half the function calls that my formula requires).

It looks like this formula approach can be generalized to find the Nth such-and-such day of the week as follows...

=1+7*Nth-WEEKDAY(EOMONTH(A2,-1),10+DoW)+EOMONTH(A2,-1)

where Nth would be the occurrence number (1 for first, 2 for second, etc.) for the DoW (day of week)(1 for Monday, 2 for Tuesday, etc.).

Here is an alternate generalized function for those who have the WORKDAY.INTL function available to them...

=WORKDAY.INTL(A2-DAY(A2),Nth,REPLACE("1111111",DoW,1,0))

As before, Nth would be the occurrence number (1 for first, 2 for second, etc.) for the DoW (day of week)(1 for Monday, 2 for Tuesday, etc.).

 

[Trevor3007]

Here is an alternate generalized function for those who have the WORKDAY.INTL function available to them...

=WORKDAY.INTL(A2-DAY(A2),Nth,REPLACE("1111111",DoW,1,0))

As before, Nth would be the occurrence number (1 for first, 2 for second, etc.) for the DoW (day of week)(1 for Monday, 2 for Tuesday, etc.).

Hi Rick,

thank you very much for your help.

Your formula works great until I put in 29/10/18 ... I should of stated that the particular worksheet is for timesheets . So when I put in the date 29/10/18 it returns 1/10/18 , but should return 5/11/18. Is this viable and if so what is the magic formula etc?


Again I thank you for your help & my apologies for not stating this in my original request for you assistance.

KR
Trevor3007

 

[Dave Patton]

Please define when you require the 1st Monday of the current month and when you require the 1st monday of the next month.

Please evaluate

Excel 2010
	A	B	C	D
15	29-Oct-18			Mon 01-Oct-18
16	29-Oct-18			Mon 05-Nov-18
3a

Cell Formulas
Range	Formula
D15	=WORKDAY.INTL(A15-DAY(A15),1,"0111111")
D16	=WORKDAY.INTL(A16,1,"0111111")

 

[Trevor3007]

Please define when you require the 1st Monday of the current month and when you require the 1st monday of the next month.

Please evaluate

Excel 2010

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]15[/TD]
[TD="align: right"]29-Oct-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Mon 01-Oct-18[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]29-Oct-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Mon 05-Nov-18[/TD]

</tbody>

3a​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D15[/TH]
[TD="align: left"]=WORKDAY.INTL(A15-DAY(A15),1,"0111111")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D16[/TH]
[TD="align: left"]=WORKDAY.INTL(A16,1,"0111111")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

good evening dave,

thank you for your help & D16 is the one I would use.

KR
Trevor3007

 

[Rick Rothstein]

Hi Rick,

Your formula works great until I put in 29/10/18 ... I should of stated that the particular worksheet is for timesheets . So when I put in the date 29/10/18 it returns 1/10/18 , but should return 5/11/18. Is this viable and if so what is the magic formula etc?

I am not sure if you have your answer yet or not, but I am curious... how do you know when the first Monday of the month for the specified date should be returned and when it should be ignored and the first Monday of the next month should be returned instead? What is the criteria you are using to make that decision?

 

[Trevor3007]

I am not sure if you have your answer yet or not, but I am curious... how do you know when the first Monday of the month for the specified date should be returned and when it should be ignored and the first Monday of the next month should be returned instead? What is the criteria you are using to make that decision?

Morning Rick,

thanks for your help. Please use link below:-

https://www.amazon.co.uk/clouddrive/share/0wQ4mjCdxysmH8zaTxXU6oM1fIVv2hBOFWlgfbUsYxF


A2 is inputted manual weekly, at present b2 is also inputted manually but only the 1st monday in the period (so for October, it is 1/10/18)

from c2 - h2 these have various formulas and are triggered from b2.

hoping it makes sense. have a great day
KR
Trevor3007