Same formula, different results...

[Michele317]

Hello

I'm having problems with the NETWORKDAYS() function.
In a workbook, I have a list of dates and I want to calculate how many Monday, Tuesday, Wednesday and Thursday there are in that list.
I do the follwing formula =NETWORKDAYS(B3;B32;"0000111";Settings!E10:E23) (where B3=1 April 2024 and B32=30 April 2024) and seems to work. The result I have it's 16, since I have also 2 holidays, which are 1 April 2024 and 25 April 2024.

I do the same formula in another workbook and I get 17 instead of 16. How is that possible?
I tried to count how many fridays there are in the list (which is another thing I need) and it worked.
I also iterate the process modifying the range of dates and it turned out there is a problem with 28 April 2024, which is a Sunday but it is taken as a workday

Thank you in advance

 

[awoohaw]

Michelle, it would probably be helpful if you shared the formulas in those two cells as well as the data (including the holiday lists for both workbooks).

 

[Michele317]

Michelle, it would probably be helpful if you shared the formulas in those two cells as well as the data (including the holiday lists for both workbooks).

I hope this helps

All the dates are written in the following format dd/mm/yyyy
WorkbookA
Sheet "Aprile 2024"
The list of dates start in B3 and ends in B32

Date List
01/04/2024
02/04/2024
03/04/2024
04/04/2024
05/04/2024
06/04/2024
07/04/2024
08/04/2024
09/04/2024
10/04/2024
11/04/2024
12/04/2024
13/04/2024
14/04/2024
15/04/2024
16/04/2024
17/04/2024
18/04/2024
19/04/2024
20/04/2024
21/04/2024
22/04/2024
23/04/2024
24/04/2024
25/04/2024
26/04/2024
27/04/2024
28/04/2024
29/04/2024
30/04/2024

WorkbookA
Sheet "Settings"
The list of dates start in E10 and ends in E23

Holiday List
01/01/2024
06/01/2024
31/03/2024
01/04/2024
25/04/2024
01/05/2024
02/06/2024
15/08/2024
01/11/2024
07/12/2024
08/12/2024
25/12/2024
26/12/2024
31/12/2024

WorkbookA
Sheet "Settings"
Formula: =NETWORKDAYS(B3;B32;"0000111")
WorkbookB
Sheet "Aprile 2024"
Formula: =NETWORKDAYSL('[WorkbookA.xlsm]Aprile 2024'!$B$3;'WorkbookA.xlsm]Aprile 2024'!$B$32;"0000111";[WorkbookA.xlsm]Settings!$E$10:$E$23)

 

[awoohaw]

are both workbooks identical? The workbook B formula has a stray "L" in the function name.

Also, the formula for workbook a does not have the HOLIDAY list, it has a weekend strings "0000111" that are normally in the NETWORKDAYS.INTL function.
And the Workbook B formula should be NETWORKDAYS.INTL function.

 

[Michele317]

are both workbooks identical? The workbook B formula has a stray "L" in the function name.

Also, the formula for workbook a does not have the HOLIDAY list, it has a weekend strings "0000111" that are normally in the NETWORKDAYS.INTL function.
And the Workbook B formula should be NETWORKDAYS.INTL function.

Yeah sorry, all correct. It Is that I had to translate the formulas to write them here, becuase i do not write them in english. In my language the formula ends with the ".INTL" and in both cases I put the holidays.
The "L" in the formula in WorkbookB is exactly the end of ".INTL", which I forgot to delete.

To recap:
Formula in WorkbookA is =NETWORKDAYS.INTL($B$3;$B$32;"0000111";Settings!$E$10:$E$23)
Formula in WorkbookB is =NETWORKDAYS.INTL('[WorkbookA.xlsm]Aprile 2024'!$B$3;'WorkbookA.xlsm]Aprile 2024'!$B$32;"0000111";[WorkbookA.xlsm]Settings!$E$10:$E$23)

This ends up with the following result:
WorkbookA: 16
WorkbookB: 17

 

[awoohaw]

I did the calculations in each workbook (I named mine Book2 and Book3). And got 4 identical results. My guess is maybe you have a 0 or 1 different in the weekend string.

Also, have you compared the holiday dates to be sure they are exact? as well as the start and end dates of your ranges?

Book3
	B	C	D	E
1
2		Workbook3:	16	=NETWORKDAYS.INTL(A3,A32,"0000111",Settings!A10:A23)
3		Workbook2:	16	=NETWORKDAYS.INTL('[Book2]Aprile 2024'!$A$3,'[Book2]Aprile 2024'!$A$32,"0000111",[Book2]Settings!$A$10:$A$23)
Aprile 2024

Cell Formulas
Range	Formula
D2	D2	=NETWORKDAYS.INTL(A3,A32,"0000111",Settings!A10:A23)
E2:E3	E2	=FORMULATEXT(D2)
D3	D3	=NETWORKDAYS.INTL('[Book2]Aprile 2024'!$A$3,'[Book2]Aprile 2024'!$A$32,"0000111",[Book2]Settings!$A$10:$A$23)

Book2
	C	D	E
2	Book2	16	=NETWORKDAYS.INTL(A$3,$A$32,"0000111",Settings!A$10:A$23)
3	Book3	16	=NETWORKDAYS.INTL('[Book3]Aprile 2024'!$A$3, '[Book3]Aprile 2024'!$A$32,"0000111", [Book3]Settings!$A$10:$A$23 )
Aprile 2024

Cell Formulas
Range	Formula
D2	D2	=NETWORKDAYS.INTL(A$3,$A$32,"0000111",Settings!A$10:A$23)
E2:E3	E2	=FORMULATEXT(D2)
D3	D3	=NETWORKDAYS.INTL('[Book3]Aprile 2024'!$A$3, '[Book3]Aprile 2024'!$A$32,"0000111", [Book3]Settings!$A$10:$A$23 )

 

[Michele317]

I did the calculations in each workbook (I named mine Book2 and Book3). And got 4 identical results. My guess is maybe you have a 0 or 1 different in the weekend string.

Book3
	B	C	D	E
1
2		Workbook3:	16	=NETWORKDAYS.INTL(A3,A32,"0000111",Settings!A10:A23)
3		Workbook2:	16	=NETWORKDAYS.INTL('[Book2]Aprile 2024'!$A$3,'[Book2]Aprile 2024'!$A$32,"0000111",[Book2]Settings!$A$10:$A$23)
Aprile 2024

Cell Formulas
Range	Formula
D2	D2	=NETWORKDAYS.INTL(A3,A32,"0000111",Settings!A10:A23)
E2:E3	E2	=FORMULATEXT(D2)
D3	D3	=NETWORKDAYS.INTL('[Book2]Aprile 2024'!$A$3,'[Book2]Aprile 2024'!$A$32,"0000111",[Book2]Settings!$A$10:$A$23)

Book2
	C	D	E
2	Book2	16	=NETWORKDAYS.INTL('Aprile 2024'!A$3,'Aprile 2024'!$A$32,"0000111",Settings!A$10:A$23)
3	Book3	16	=NETWORKDAYS.INTL('[Book3]Aprile 2024'!$A$3, '[Book3]Aprile 2024'!$A$32,"0000111", [Book3]Settings!$A$10:$A$23 )
Aprile 2024

Cell Formulas
Range	Formula
D2	D2	=NETWORKDAYS.INTL('Aprile 2024'!A$3,'Aprile 2024'!$A$32,"0000111",Settings!A$10:A$23)
E2:E3	E2	=FORMULATEXT(D2)
D3	D3	=NETWORKDAYS.INTL('[Book3]Aprile 2024'!$A$3, '[Book3]Aprile 2024'!$A$32,"0000111", [Book3]Settings!$A$10:$A$23 )

Thank you a lot for the help. I just figured out the problem. In WorkbookA I was using "Date system 1904", in WorkbookB no, because I need negative time too. I thought that was a change in the settings that would have worked for all the Workbooks, already created and to be created.
Thank you again for your help

 

[awoohaw]

okay, I never would have thought of that.

Best Wishes!