List of bi-monthly dates for whole year

[JackDanIce]

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

=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

 

[steve the fish]

Hi Jack. What do you mean by middle of the month? 15th? Your formula is just doing every two weeks. Its always going to creep towards start of the month if using weeks.

 

[JackDanIce]

Hey @steve the fish
Yeah, that's trying to proxy middle of month but because most months are >28 days, it gives the drift

Two dates per month, whatever the nearest workday is -1 from the middle of the month and -1 from last workday of month

It's to calculate a payment schedule where payments are made bi-monthly and notificaiton day before for alert

 

[steve the fish]

Test this out:

=WORKDAY(IF(MOD(ROW()/2,1),EOMONTH(DATE(2021,1,1),ROUNDUP((ROW()-3)/2,0)),EDATE(DATE(2021,1,15),ROUNDUP((ROW()-2)/2,0))),-1)

Its intended for the first row used to be row 2. Will need some adjustment if not.

 

[JackDanIce]

I thought of using MOD with ROW too, let me give that a go, thanks!

 

[JackDanIce]

Modified slightly, but thank you, your suggestion works when formula is dragged down required number of rows:

=LET(start_date,DATE(2021,1,1),WORKDAY(IF(MOD(ROW()*0.5,1),EOMONTH(start_date,ROUNDUP((ROW()-3)*0.5,0)),EDATE(start_date+14,ROUNDUP((ROW()-2)*0.5,0))),-1))

Any ideas for a dynamic or single cell/array formula instead?

 

[steve the fish]

I cant think how you can have dynamic formula if you dont want the number hardcoded.

 

[JackDanIce]

That's ok, I can use this for now Cheers!

 

[Dave Patton]

T202008b.xlsm
	A
1	Thu 31-Dec-20
2	Thu 14-Jan-21
3	Fri 29-Jan-21
1f

Cell Formulas
Range	Formula
A2:A3	A2	=IF(DAY(A1)>15,WORKDAY(EOMONTH(A1,0)+15,-1),WORKDAY(EOMONTH(A1,0),-1))

 

[JackDanIce]

Thank you, I've changed and this gets me closer:

=WORKDAY(EOMONTH(B2,0)+15*(DAY(B2)>15),-2)

Other.xlsm
	B
2	31/12/2020
3	13/01/2021
4	28/01/2021
5	11/02/2021
6	25/02/2021
7	11/03/2021
8	29/03/2021
9	13/04/2021
10	28/04/2021
11	13/05/2021
12	27/05/2021
13	11/06/2021
14	28/06/2021
15	13/07/2021
16	29/07/2021
17	12/08/2021
18	27/08/2021
19	13/09/2021
20	28/09/2021
21	13/10/2021
22	28/10/2021
23	11/11/2021
24	26/11/2021
25	13/12/2021
26	29/12/2021
27	13/01/2022
Data

Cell Formulas
Range	Formula
B3:B27	B3	=WORKDAY(EOMONTH(B2,0)+15*(DAY(B2)>15),-2)