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
 

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
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
That's ok, I can use this for now :) Cheers!
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

Forum statistics

Threads
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?

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