BusinessDay List in Calendar Table

GreyFox8991

New Member
Joined
Jul 20, 2022
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
Greetings Excel Community,

I am creating a Calendar Table from Scratch and I am wondering if there is a way to create a running list of "Business Days" in the year. For example:

01/01/2022- 0 (Not a Business day as it is a Weekend)
01/02/2022- 0 (Not a Business day as it is a Weekend)
01/03/2022-1
01/04/2022-2
01/05/2022-3
12/31/2022-255
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Maybe in first cell:
Code:
=TEXT(DATE(2022,1,ROWS($1:1)),"MM/DD/YYYY") & "-"& (WEEKDAY(DATE(2022,1,ROWS($1:1)),2)<5)*1
drag down
 
Upvote 0
Hi Bebo,

Thank you for the reply... I tried the formula but It its returning "0".

To be more specific in column Date I have the dates and I hope to return the Business Day value in Column YearBusinessDay instead of 01/01/2022-0.

So I would like the end result to be for the value in YearBusinessDay to be the business day of the year based on the Date column. so 01/06/2022 should return 4.

Hope this helps. I am still trying to figure it out!

1658372759750.png
 
Upvote 0
Does this do what you want?

22 07 21.xlsm
AB
1DateTBD
201-Jan-220
302-Jan-220
403-Jan-221
504-Jan-222
605-Jan-223
706-Jan-224
807-Jan-225
908-Jan-220
1009-Jan-220
1110-Jan-226
1211-Jan-227
1312-Jan-228
1413-Jan-229
1514-Jan-2210
1615-Jan-220
1716-Jan-220
1817-Jan-2211
1918-Jan-2212
35722-Dec-22254
35823-Dec-22255
35924-Dec-220
36025-Dec-220
36126-Dec-22256
36227-Dec-22257
36328-Dec-22258
36429-Dec-22259
36530-Dec-22260
36631-Dec-220
Work Days
Cell Formulas
RangeFormula
B2:B19,B357:B366B2=IF(WEEKDAY(A2,2)>5,0,MAX(B$1:B1)+1)
 
Upvote 0
Hello Bebo and Peter,

Both solutions are valid. I thank you for your time to review this and provide a solution. I will mark as solution.

Thank you both!
 
Upvote 0
You're welcome. Glad we could help. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,223,945
Messages
6,175,555
Members
452,652
Latest member
eduedu

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