Updating formula to not use the SEQUENCE command

troybowman

New Member
Joined
Nov 13, 2023
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
=SUM(N(MOD(SEQUENCE(DAY(A1),,WEEKDAY(EDATE(EOMONTH(A1,0)+1,-1))),7)=WEEKDAY(A1)))

How can I rewrite this without using the SEQUENCE command so it will work on Excel 2016

Formula will display the calendar day as first monday, second friday, etc.

A1 is the date
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Please explain what you want the formula to do.
An example would help.
 
Upvote 0
possibly

T202311a.xlsm
ABCDE
1
2Mon 13-Nov-232Monday0111111
3Tue 14-Nov-232Tuesday1011111
4Wed 15-Nov-233Wednesday1101111
5
3c
Cell Formulas
RangeFormula
B2:B4B2=NETWORKDAYS.INTL(A2-DAY(A2)+1,A2,VLOOKUP(TEXT(A2,"dddd"),$D$2:$E$4,2))
 
Upvote 0
Please explain what you want the formula to do.
An example would help.
Example
Today is November 13th. It is the Second Monday of November.
November 12, 2022 is the Second Monday of November.
October 12, 2021 is the Second Tuesday of October.
Etc.
 
Upvote 0
How about
Excel Formula:
=NETWORKDAYS.INTL(EOMONTH(A1,-1)+1,A1,REPLACE("1111111",WEEKDAY(A1,2),1,0))
 
Upvote 0
Solution
Did you try the suggestion?

T202311a.xlsm
ABCD
1
2Mon 13-Nov-2322
3Tue 14-Nov-2322
4Wed 15-Nov-2333
5
6
7Mon 13-Nov-232
8Sat 12-Nov-222
9Tue 12-Oct-212
3c
Cell Formulas
RangeFormula
B2:B4B2=NETWORKDAYS.INTL(A2-DAY(A2)+1,A2,VLOOKUP(TEXT(A2,"dddd"),$E$2:$F$4,2))
C2:C4,C7:C9C2=NETWORKDAYS.INTL(A2-DAY(A2)+1,A2,REPLACE("1111111",MATCH(TEXT(A2,"ddd"),{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0),1,0),$G$2)
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Works like a charm!
Your choice of course to decide what to use, but in case you didn't try it, the simpler option in post#7 returns exactly the same result as the formula in post#5.
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

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
Back
Top