NETWORKDAYS for specific months

G

Gage

New Member
Joined
Mar 26, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Howdy,

I am trying to track how much of each months departmental holiday budget has been used.

However, I am struggling to get NETWORKDAYS to calculate the individual months in the header when someone has requested holiday spanning more than one month, like March to April. Kindly see example below- formula works fine but only for counting the total days, not working days.

1711466295796.png


Any help would be appreciated, significantly.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Gage said:
Howdy,

I am trying to track how much of each months departmental holiday budget has been used.

However, I am struggling to get NETWORKDAYS to calculate the individual months in the header when someone has requested holiday spanning more than one month, like March to April. Kindly see example below- formula works fine but only for counting the total days, not working days.

View attachment 108999

Any help would be appreciated, significantly.
Click to expand...
i think that the error is a formatting one
since the example is using European dates

for the first three rows since we have nonstandard dates they must be converted

the correct syntax for networkdays is

start date, end date, holiday dates (this can be a range or a variable)

BT DELAYS.xlsx
ABCDEFGHIJKLMNO
26/3/202413/03/2024603/06/202403/13/2024 45364
316/02/202412/3/20241802/16/202403/12/202445338 
429/03/20247/4/2024603/29/202404/07/202445380 
Sheet3
Cell Formulas
RangeFormula
K2:L4K2=IF($A2="","",IF($B2="","",IF(ISNUMBER(A2),TEXT(A2,"dd/mm/yyyy"),TEXT(N2,"mm/dd/yyyy"))))
N2:O4N2=IF($A2="","",IF($B2="","",IF(ISNUMBER(A2),"",DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)))))
E2:E4E2=IF($A2="","",IF($B2="","",NETWORKDAYS(K2,L2)))


please note that i have the side work in columns ("k" though "o") that are not used (this can be moved anywhere on the row, even whited out so not be visable)
then the netowrk days is calculated for the entire period

if this is not what you are looking for please let me know

also, since you are using formulas that is what i presented to you.

so here is a breakdown of the formulas and what they are doing

the if a2 and b2 checks are error checks to only process data IF data is present (if nothing is there, there is no output)
IF($A2="","" & IF($B2="",""

then we check for a valid date
IF(ISNUMBER(A2)

if there is no valid date, the formula then converts a NON date into a date number
DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2))

and finally, converts the date value into a valid date and then provides you with the number of work days
TEXT(N3,"mm/dd/yyyy")

and
NETWORKDAYS(K2,L2)

hope that helps
 
Last edited:
Upvote 0
You did not specify the relevant Holidays. You can edit the formulas to refer to your list of holidays dates.

Work Days.xlsm
ABCDEFG
1StartEndTotalBlank29-Feb-2431-Mar-2430-Apr-24
206-Mar-2413-Mar-246060
316-Feb-2412-Mar-24181080
429-Mar-2407-Apr-246015
5
1e
Cell Formulas
RangeFormula
E2:G4E2=MAX(0,IF($A2<E$1,NETWORKDAYS(MIN($A2,E$1),MIN(E$1,$B2)))-SUM($D2:D2))
C2:C4C2=NETWORKDAYS(A2,B2)
 
Upvote 0
Solution
since the holidays were not listed in the OP#1. they were not included in the formulas. but you are correct.
I also said in my example that the holidays can be added using a range or a variable, which is an easy addition.

simply defining the standard holidays into a set range
list of US federal holidays can easily be incorporated and/or edited

Federal
BCDE
22024 Federal Holidays
3Holiday NameDay of WeekObserved Date
4New Year's DayMondayJanuary 1, 2024
5Birthday of Martin Luther King, Jr.MondayJanuary 15, 2024
6Washington's BirthdayMondayFebruary 19, 2024
7Memorial DayMondayMay 27, 2024
8JuneteenthWednesdayJune 19, 2024
9Independence DayThursdayJuly 4, 2024
10Labor DayMondaySeptember 2, 2024
11Columbus DayMondayOctober 14, 2024
12Veterans DayMondayNovember 11, 2024
13Thanksgiving DayThursdayNovember 28, 2024
14Christmas DayWednesdayDecember 25, 2024
2024
Cell Formulas
RangeFormula
C4:C14C4=TEXT(WEEKDAY(D4),"dddd")
 
Upvote 0
or since i think the OP is in the UK
 

Attachments

  • Bank Holidays in United Kingdom in 2022 Office Holidays.jpg
    Bank Holidays in United Kingdom in 2022 Office Holidays.jpg
    143.4 KB · Views: 24
Upvote 0
so, with the addition of the US federal holidays

networkdays (mrexcel).xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1descdate valuecal date$AE$2$AE$11$AE$2:$AE$11
26/3/202413/03/2024603/06/202403/13/2024 45364New Year's Day4529201/01/2024
316/02/202412/3/20241702/16/202403/12/202445338 Birthday of Martin Luther King, Jr.4530601/15/2024
429/03/20247/4/2024603/29/202404/07/202445380 Washington's Birthday4534102/19/2024
5     Memorial Day4543905/27/2024
6 Juneteenth4546206/19/2024
7 Independence Day4547707/04/2024
8 Labor Day4553709/02/2024
9 Columbus Day4557910/14/2024
10 Veterans Day4560711/11/2024
11Thanksgiving Day4562411/28/2024
12Christmas Day4565112/25/2024
13
Sheet3
Cell Formulas
RangeFormula
AG1AG1=ADDRESS(COUNTA($AE$2:$AE$51),31)
AH1AH1=AF1&":"&AG1
K2:L5K2=IF($A2="","",IF($B2="","",IF(ISNUMBER(A2),TEXT(A2,"dd/mm/yyyy"),TEXT(N2,"mm/dd/yyyy"))))
N2:O4,N5N2=IF($A2="","",IF($B2="","",IF(ISNUMBER(A2),"",DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)))))
O5O5=IF($A5="","",IF($B5="","",IF(ISNUMBER(B5),,DATE(RIGHT(B5,4),MID(B5,4,2),LEFT(B5,2)))))
E2:E10E2=IF($A2="","",IF($B2="","",NETWORKDAYS(K2,L2,INDIRECT($AH$1))))
AE2:AE12AE2=TEXT(AD2,"mm/dd/yyyy")


EXPLAINED
simply adding the US federal holidays as a specific range solves this issue
so with this example, the formula uses a dynamic range to look for any holidays listed
the dynamic range is defined to look up a specific range that changes based on the volume of dates listed in the AE column

UK or any other holidays needed, can simply be added to the list and the range will auto update

NOTES:
networkdays will automatically include the holiday range as NON worked days

as before, you can move or hide the calculations ranges k2 through o10

networkdays (mrexcel).xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1descdate valuecal date$AE$2$AE$11$AE$2:$AE$11
26/3/202413/03/2024603/06/202403/13/2024 45364New Year's Day4529201/01/2024
316/02/202412/3/20241702/16/202403/12/202445338 Birthday of Martin Luther King, Jr.4530601/15/2024
429/03/20247/4/2024603/29/202404/07/202445380 Washington's Birthday4534102/19/2024
5     Memorial Day4543905/27/2024
6     Juneteenth4546206/19/2024
7     Independence Day4547707/04/2024
8     Labor Day4553709/02/2024
9     Columbus Day4557910/14/2024
10 Veterans Day4560711/11/2024
11Thanksgiving Day4562411/28/2024
12Christmas Day4565112/25/2024
13
Sheet3
Cell Formulas
RangeFormula
AG1AG1=ADDRESS(COUNTA($AE$2:$AE$51),31)
AH1AH1=AF1&":"&AG1
K2:L9K2=IF($A2="","",IF($B2="","",IF(ISNUMBER(A2),TEXT(A2,"dd/mm/yyyy"),TEXT(N2,"mm/dd/yyyy"))))
N2:O9N2=IF($A2="","",IF($B2="","",IF(ISNUMBER(A2),"",DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)))))
E2:E10E2=IF($A2="","",IF($B2="","",NETWORKDAYS(K2,L2,INDIRECT($AH$1))))
AE2:AE12AE2=TEXT(AD2,"mm/dd/yyyy")
 
Upvote 0
Dave Patton said:
You did not specify the relevant Holidays. You can edit the formulas to refer to your list of holidays dates.

Work Days.xlsm
ABCDEFG
1StartEndTotalBlank29-Feb-2431-Mar-2430-Apr-24
206-Mar-2413-Mar-246060
316-Feb-2412-Mar-24181080
429-Mar-2407-Apr-246015
5
1e
Cell Formulas
RangeFormula
E2:G4E2=MAX(0,IF($A2<E$1,NETWORKDAYS(MIN($A2,E$1),MIN(E$1,$B2)))-SUM($D2:D2))
C2:C4C2=NETWORKDAYS(A2,B2)
Click to expand...

Hi Dave


This is exactly what I need but when I try to recreate it, I do not get the same results as you and I am unsure why. Hopefully it's something obvious I've missed?


1711532574285.png
 
Upvote 0
you do realize that all the work here was mine...
clearly not..
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
How can I exactly count NETWORKDAYS in multiple date ranges, after a specific date?
Replies
1
Views
204
Tetra201
Tetra201
M
  • Question Question
Formula for unpaid leave adjustment
Replies
2
Views
117
Joe4
Joe4
J
  • Question Question
Dynamic calculations for capacity
Replies
2
Views
77
Jali1992
J
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
396
dshtesy
D
A
  • Solved
Error with working days in a month
Replies
5
Views
503
Anthony47
A

Forum statistics

Threads
1,224,521
Messages
6,179,290
Members
452,902
Latest member
Knuddeluff

Share this page

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