# List of bi-monthly dates for whole year

#### JackDanIce

##### Well-known Member
Hi,

I'm trying to make a list of all dates in 2021 that is 1 workday before middle of the month and 1 workday before the end of the month.

I have this, but not all values look correct:

 31/12/2020 14/01/2021 28/01/2021 11/02/2021 25/02/2021 11/03/2021 25/03/2021 08/04/2021 22/04/2021 06/05/2021 20/05/2021 03/06/2021 17/06/2021 01/07/2021 15/07/2021 29/07/2021 12/08/2021 26/08/2021 09/09/2021 23/09/2021 07/10/2021 21/10/2021 04/11/2021 18/11/2021 02/12/2021 16/12/2021 30/12/2021

e.g. 8 April 2021 which should be 13 April 2021
Formula in C3
Excel Formula:
``=WORKDAY(SEQUENCE(27,1,DATEVALUE("01/01/2021"),14),-1)``
Can anyone suggest correction? Ideally without hardcoding the number of rows as 27?

TIA,
Jack

#### Dave Patton

##### Well-known Member
Possibly with that construct =WORKDAY(EOMONTH(B1,0)+(DAY(B1)>15)*15,-1)

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### JackDanIce

##### Well-known Member
It works when it's -2 for the dates I need, thank you

Replies
4
Views
157
Replies
2
Views
74
Replies
12
Views
342
Replies
0
Views
649
Replies
11
Views
168

1,148,368
Messages
5,746,287
Members
424,006
Latest member
Metal_warrior

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back