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

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

steve the fish

Well-known Member
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

Well-known Member
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

Well-known Member
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

Well-known Member

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

JackDanIce

Well-known Member
Modified slightly, but thank you, your suggestion works when formula is dragged down required number of rows:
Excel Formula:
``=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

Well-known Member

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

JackDanIce

Well-known Member
That's ok, I can use this for now Cheers!

Dave Patton

Well-known Member
T202008b.xlsm
A
1Thu 31-Dec-20
2Thu 14-Jan-21
3Fri 29-Jan-21
1f
Cell Formulas
RangeFormula
A2:A3A2=IF(DAY(A1)>15,WORKDAY(EOMONTH(A1,0)+15,-1),WORKDAY(EOMONTH(A1,0),-1))

JackDanIce

Well-known Member
Thank you, I've changed and this gets me closer:
Excel Formula:
``=WORKDAY(EOMONTH(B2,0)+15*(DAY(B2)>15),-2)``
Cell Formulas
RangeFormula
B3:B27B3=WORKDAY(EOMONTH(B2,0)+15*(DAY(B2)>15),-2)

Replies
4
Views
98
Replies
12
Views
263
Replies
0
Views
559
Replies
6
Views
337
Replies
5
Views
99

1,141,001
Messages
5,703,656
Members
421,308
Latest member
NewBlood

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?

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

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