BusinessDay List in Calendar Table

[GreyFox8991]

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

 

[bebo021999]

Maybe in first cell:

=TEXT(DATE(2022,1,ROWS($1:1)),"MM/DD/YYYY") & "-"& (WEEKDAY(DATE(2022,1,ROWS($1:1)),2)<5)*1

drag down

 

[GreyFox8991]

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!

 

[bebo021999]

Is it are you looking for?

Book1
	A	B
1	01-Jan	0
2	02-Jan	0
3	03-Jan	1
4	04-Jan	2
5	05-Jan	3
6	06-Jan	4
7	07-Jan	5
8	08-Jan	5
9	09-Jan	5
10	10-Jan	6
11	11-Jan	7
12	12-Jan	8
13	13-Jan	9
14	14-Jan	10
15	15-Jan	10
Sheet1

Cell Formulas
Range	Formula
B1:B15	B1	=NETWORKDAYS($A$1,A1)
A2:A15	A2	=A1+1

 

[Peter_SSs]

Does this do what you want?

22 07 21.xlsm
	A	B
1	Date	TBD
2	01-Jan-22	0
3	02-Jan-22	0
4	03-Jan-22	1
5	04-Jan-22	2
6	05-Jan-22	3
7	06-Jan-22	4
8	07-Jan-22	5
9	08-Jan-22	0
10	09-Jan-22	0
11	10-Jan-22	6
12	11-Jan-22	7
13	12-Jan-22	8
14	13-Jan-22	9
15	14-Jan-22	10
16	15-Jan-22	0
17	16-Jan-22	0
18	17-Jan-22	11
19	18-Jan-22	12
357	22-Dec-22	254
358	23-Dec-22	255
359	24-Dec-22	0
360	25-Dec-22	0
361	26-Dec-22	256
362	27-Dec-22	257
363	28-Dec-22	258
364	29-Dec-22	259
365	30-Dec-22	260
366	31-Dec-22	0
Work Days

Cell Formulas
Range	Formula
B2:B19,B357:B366	B2	=IF(WEEKDAY(A2,2)>5,0,MAX(B$1:B1)+1)

 

[GreyFox8991]

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!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.