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 | | | |
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
3a
Worksheet Formulas
Cell | 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") |
---|
<thead>
</thead><tbody>
</tbody> |
<tbody>
</tbody>
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;">
=WORKDAY.INTL(MAX(A2-DAY(A2),(DAY(A2)>21)*(EOMONTH(A2,0))),1,"0111111") |
<tbody>
</tbody>
</body>