List of bi-monthly dates for whole year

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
5,031
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
It works when it's -2 for the dates I need, thank you
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top