Excel formula in two spots of same work sheet not working properly

[kismatta]

Hello,

I am using the NETWORKDAYS formula in two separate sections on the same worksheet, however one of them is not calculating properly. Of note, I use the www.timeanddate.com/date/workdays site as a double check method to confirm the calculations with an exclusion of weekends and public holidays in Canada.

Location A formula: =NETWORKDAYS(H22,I22,B76:B158)

From YYYY-MM-DD	To YYYY-MM-DD	# of Working Hrs (8hrs/day)	# of Working Days
2021-05-18	2021-08-12	480.000	60.00

Location B formula: =NETWORKDAYS(B55,C55,B76:B158)

From YYYY-MM-DD	To YYYY-MM-DD	# of Working Hrs (8hrs/day)	# of Working Days
2023-07-02	2023-12-31	992.000	124.00

The # of working days in location B should be 126.

I need to amend the dates in location B to only reflect one day, 2023-07-02, however it gives me a result of 0. So I tried entering 2023-07-02 to 2023-07-03, it still gives me a 0. Only when I enter 2023-07-04 does it calculate, however it gives me an answer of 8.00 (# of working hrs) and 1.00 (# of working days), when it's clearly 2 days.

From YYYY-MM-DD	To YYYY-MM-DD	# of Working Hrs (8hrs/day)	# of Working Days
2023-07-02	2023-07-04	8.000	1.00

Any help is greatly appreciated. I'm at a loss as to why the formula does not work properly in both locations.

thank you!

 

[Fluff]

Hi & welcome to MrExcel.
What dates do you have in B76:B158?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[kismatta]

Hi & welcome to MrExcel.
What dates do you have in B76:B158?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hello, and thank you for your prompt response and helpful tips. Unfortunately, I am working on a computer that will not allow me to download add-ons. B76:B158 is a holiday calendar added at the bottom of the sheet.

I am not sure if you are open to me emailing you the spreadsheet or some other method?

 

[Fluff]

Can you just copy/paste the range B76:B158

 

[kismatta]

Can you just copy/paste the range B76:B158

Here is the data:

2020-01-01	New Year's Day
2020-04-10	Good Friday
2020-04-13	Easter Monday
2020-05-18	Victoria Day
2020-07-01	Canada Day
2020-08-03	Civic Holiday
2020-09-07	Labour Day
2020-10-12	Thanksgiving
2020-11-11	Remembrance Day
2020-12-25	Christmas Day
2020-12-28	Boxing Day (Lieu)
2021-01-01	New Year's Day
2021-04-02	Good Friday
2021-04-05	Easter Monday
2021-05-24	Victoria Day
2021-07-01	Canada Day
2021-08-02	Civic Holiday
2021-09-06	Labour Day
2021-09-30	Truth & Reconciliation
2021-10-11	Thanksgiving
2021-11-11	Remembrance Day
2021-12-27	Christmas Day (Lieu)
2021-12-28	Boxing Day (Lieu)
2022-01-03	New Year's Day (Lieu)
2022-04-15	Good Friday
2022-04-18	Easter Monday
2022-05-23	Victoria Day
2022-01-01	Canada Day
2022-08-01	Civic Holiday
2022-09-05	Labour Day
2022-09-30	Truth & Reconciliation
2022-10-10	Thanksgiving
2022-11-11	Remembrance Day
2022-12-26	Boxing Day
2022-12-27	Christmas Day (Lieu)
2023-01-02	New Year's Day (Lieu)
2023-04-07	Good Friday
2023-04-10	Easter Monday
2023-05-22	Victoria Day
2023-07-03	Canada Day (Lieu)
2023-08-07	Civic Holiday
2023-09-04	Labour Day
2022-10-02	Truth & Reconciliation (Lieu)
2023-10-09	Thanksgiving
2022-11-13	Remembrance Day
2023-12-25	Christmas Day
2023-12-26	Boxing Day (Lieu)
2024-01-01	New Year's Day
2024-03-29	Good Friday
2024-04-01	Easter Monday
2024-05-20	Victoria Day
2024-07-01	Canada Day
2024-08-05	Civic Holiday
2024-09-02	Labour Day
2024-09-30	Truth & Reconciliation
2024-10-14	Thanksgiving
2024-11-11	Remembrance Day
2024-12-25	Christmas Day
2024-12-26	Boxing Day
2025-01-01	New Year's Day
2025-04-18	Good Friday
2025-04-21	Easter Monday
2025-05-19	Victoria Day
2025-01-01	Canada Day
2025-08-04	Civic Holiday
2025-09-01	Labour Day
2025-09-30	Truth & Reconciliation
2025-10-13	Thanksgiving
2025-11-11	Remembrance Day
2025-12-25	Christmas Day
2025-12-26	Boxing Day
2026-01-01	New Year's Day
2026-04-03	Good Friday
2026-04-06	Easter Monday
2026-05-18	Victoria Day
2026-01-01	Canada Day
2026-08-03	Civic Holiday
2026-09-07	Labour Day
2026-09-30	Truth & Reconciliation
2026-10-12	Thanksgiving
2026-11-11	Remembrance Day
2026-12-25	Christmas Day
2026-12-28	Boxing Day (Lieu)

 

[Fluff]

Thanks for that. The reason that the 2nd to the 4th of July return 1 day is that the 2nd is a Sunday & the 3rd is in the list of holidays.
Also you have 6 holiday dates between the 2nd July 23 & the 31st Dec 23, which is why the formula returns 124 days which is correct

 

[kismatta]

Thank you....I'm so embarrassed I didn't look at it from that perspective. Thank you for pointing that out to me. I think I was expecting a different answer because the leave dates entered are for employees who work weekends too, however our calculation is based on a 40 hr work week, excluding weekends and holidays. So when the leave came back with a "0", it made me think that the calculation wasn't working properly. Other than a manual entry, I am not familiar enough with formulas to know if there is a way to have it account for situations such as this.

 

[Fluff]

You're welcome & thanks for the feedback.