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

 

[Dave Patton]

Rick and I both asked how you determine whether you want the date for the current or subsequent month.
if you specify the criteria, we can probably provide a formula.

 

[Dave Patton]

In the examples below, we want to show the first Monday if the date is less than or equal to 21.
If the day in the referenced cell is greater than 21, show the first Monday in the next month.

You can choose the formula that you prefer and hopefully you can customize it to meet your requirements.

Excel 2010
	A	B	C
2	4-Oct-18	Mon 01-Oct-18	Mon 01-Oct-18
3	22-Oct-18	Mon 05-Nov-18	Mon 05-Nov-18
4
3a

Cell Formulas
Range	Formula
B2	=IF(DAY(A2)>21,WORKDAY.INTL(EOMONTH(A2,0),1,"0111111"),WORKDAY.INTL(A2-DAY(A2),1,"0111111"))
C2	=WORKDAY.INTL(MAX(A2-DAY(A2),(DAY(A2)>21)*(EOMONTH(A2,0))),1,"0111111")

 

[Trevor3007]

In the examples below, we want to show the first Monday if the date is less than or equal to 21.
If the day in the referenced cell is greater than 21, show the first Monday in the next month.

You can choose the formula that you prefer and hopefully you can customize it to meet your requirements.

Excel 2010

	A	B	C

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

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

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/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] "]B2[/TH]
[TD="align: left"]=IF(DAY(A2)>21,WORKDAY.INTL(EOMONTH(A2,0),1,"0111111"),WORKDAY.INTL(A2-DAY(A2),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] "]C2[/TH]
[TD="align: left"]=WORKDAY.INTL(MAX(A2-DAY(A2),(DAY(A2)>21)*(EOMONTH(A2,0))),1,"0111111")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

evening Dave,

thank you for your help. I used
=WORKDAY.INTL(MAX(A2-DAY(A2),(DAY(A2)>21)*(EOMONTH(A2,0))),1,"0111111")

and works great.

but.... on the screen shot link I send to you , cell f2 tells me what number it is in the applicable pay period.
for 29/10/18 it should be 1 , but returns 5

formula used is
=IF(WEEKDAY(A2,2) - DAY(A2) >= 5,WEEKNUM(DATE(YEAR(A2),MONTH(A2),0),2) - WEEKNUM(DATE(YEAR(A2),MONTH(A2)-1,1),2)
+ (WEEKDAY(DATE(YEAR(A2),MONTH(A2)-1,1),2) < 6),
WEEKNUM(A2,2) - WEEKNUM(DATE(YEAR(A2),MONTH(A2),1),2)
+ (WEEKDAY(DATE(YEAR(A2),MONTH(A2),1),2) < 6))




any ideas & thank you for your help...

****** id="cke_pastebin" style="position: absolute; top: -1.52588e-05px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

<tbody>
[TD="align: left"]=WORKDAY.INTL( MAX(A2-DAY(A2),(DAY(A2)>21)*(EOMONTH(A2,0))),1,"0111111" )[/TD]

</tbody>

</body>

 

[Dave Patton]

1. I did not open your file. Security concerns.

2. I do not know if your latest question relates at all to your original question. A2 is your input cell.

3. You may want to provide a clear example, information, and expected results.

 

[Trevor3007]

Rick and I both asked how you determine whether you want the date for the current or subsequent month.
if you specify the criteria, we can probably provide a formula.

Hi,
thank you for your reply & sorry for my late reply, email issue.

I am sure I replied to you both...my sincere apologies if either have not received. Thanks you both.


KR
Trevor3007

 

[Trevor3007]

Hi Rick,

I am unsure if I thanked you for your recent help. My sincere apologies & a big THANK YOU .

KR
Trevor3007

 

[Trevor3007]

1. I did not open your file. Security concerns.

2. I do not know if your latest question relates at all to your original question. A2 is your input cell.

3. You may want to provide a clear example, information, and expected results.

Hi Dave

just to say that the link is safe & have used often . It will take you to my Amazon drive which is where the screen shot is located.

KR
Trevor3007