Same formula, different results...

Michele317

New Member
Joined
Apr 29, 2024
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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).
 
Upvote 1
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)
 
Upvote 0
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.
 
Upvote 1
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
 
Upvote 0
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?

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



Cell Formulas
RangeFormula
D2D2=NETWORKDAYS.INTL(A$3,$A$32,"0000111",Settings!A$10:A$23)
E2:E3E2=FORMULATEXT(D2)
D3D3=NETWORKDAYS.INTL('[Book3]Aprile 2024'!$A$3, '[Book3]Aprile 2024'!$A$32,"0000111", [Book3]Settings!$A$10:$A$23 )
 
Upvote 1
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.

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



Cell Formulas
RangeFormula
D2D2=NETWORKDAYS.INTL('Aprile 2024'!A$3,'Aprile 2024'!$A$32,"0000111",Settings!A$10:A$23)
E2:E3E2=FORMULATEXT(D2)
D3D3=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 :)
 
Upvote 0
Solution
okay, I never would have thought of that.

Best Wishes!
 
Upvote 1

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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