How to Calculate the Number of Working Days between two dates in Excel | NETWORKDAYS function

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/networkdays-file

[@bornwithoutboundariestarot]

Leila, can you assign criteria to "NETWORKDAYS"... as in, only counting the days while a project is in a certain phase. I have my columns assigned as "Asset Type", "Process", and Start and End date. I want to track and average of how many NETWORKDAYS each "Asset Type" stays in each "Process" phase (there are 10 possible inputs for this). The reason is it will be beneficial to my team to track the average of how long each asset type is taking us to produce, and how long we stay on each stage. This analysis will help us create best practices to improve process efficiencies.

[@amandeepsinghpannu1372]

What to do when Statutory holiday falls on the weekend? In Canada we get Monday off as a holiday in addition to Saturday Sunday, when stat holiday falls on the weekend (so 3 days off instead of 2 days). NETWORKDAY HOLIDAY function ignores the holidays on the weekend.

[@garzobela]

In Hungary in addition to it we have replaced workdays to give us longer holidays, so some saturdays are workdays to be in even to some weekdays what we have free, to solve this problem at my company I had to use several helping columns, as holidays, workdays-but-free, free-but-workday, and from them I made a column of formulas next to all of the days listed with the title of workday-or-not (T/F). From this column it's easy to calculate every duration and finishing time :) If someone has a better idea in mind, feel appreciated to share it :)

[@tazrianalamjitu]

How to insert this calendar in sheet

[@mattfirestone1]

how do i make it so that the # of working days is a dynamic formula, basically I want the working days output to be an array. My use case: I have to array's of dates, both of which are 1 column wide by x number of rows. I need to create a third array which will be 1 column wide by x number of rows, where each entry is the networkdays between the corresponding date ranges. You can't simply put {=(NETWORKDAYS(A1#,B1#) as the function NETWORKDAYS won't work with array's.

[@TaraJohnston-k4u]

Hello. This video has helped me so much. Thank you. I am still receiving an error. I am calculating how long it is taking my employees to complete a task so I am not including the start date. I added 1 to the start date as you said. It is working for almost all of my dates except when they complete the task the same day. Start Date = End Date is not working. I am getting a negative 2 (-2) for my answer. Any suggestions?

[@qaz9258]

I created a spread sheet that includes floating holidays in my holiday list. These dates update when I change the date in the "year" cell ($J$1) to the correct dates for that year. This year I want to include dates I had no need for previously BUT I cannot recall how I used this formula 🤣which is as follows =DATE($J$1,5,31)-CHOOSE(WEEKDAY(DATE($J$1,5,31)),6,0,1,2,3,4,5) Have you created a tutorial on how to do this? In the mean time I will go chew on my formula to comprehend what I did.🤔 Edit- I think CHOOSE is selecting what to subtract (0-6 ) from the DATE based on the WEEKDAY

[@qaz9258]

Think I figured out what I did. =DATE($J$1,5,31)-CHOOSE(WEEKDAY(DATE($J$1,5,31)),6,0,1,2,3,4,5) (This is the Max date possible) - (a max of 6 days based on what day of the week the Max falls on.) For example Memorial day is the LAST Monday of May, which could be MAX 31st MIN 25th A difference of 6 days. So if the 31st is Day 4 (Wednesday} of the week starting from Sunday then we would subtract 2 days from the date to arrive at Monday. If the 31st is day 2 (Monday) of the week we would subtract 0 and stay at Monday. AND if 31st is day 1 (Sunday) we would subtract 6 from the date going back to the previous Monday. The order of the numbers for CHOOSE is important. If the Holiday falls on a Sunday then start with 0. Sunday is day one so 0 needs to be in spot one. Thanks Giving is Thursdays spot 5 Needs to be where the count starts so 0 should be there. As you can see in the example Memorial day is Monday so 0 is in spot 2. Wow does that make sense? Can you make this simpler?

[@ia38035]

Hi Leila.. Thank you for a wonderful youtube series.. Need a quick help --> Can we use the formula as mentioned here: =NETWORKDAYS(1-Apr-2022, 30-Apr-2022) Please help confirm

[@amrelgammal2353]

The function uses 2 days as a weekend, but what if the weekend is one day only how we can fix it?

[@Franceskineos]

So the formula =NOT(NETWORKDAYS(DATE,DATE,HOLYDAY)) give TRUE if DATE is a weekend or is in HOLYDAY range 👍

[@nancikalidin8629]

Hi Leila.i have a long list of end dates with format mm/dd/yyyy and I have to calculate the age difference between today's date (current date change daily, 15/01/2023,16/02/2023 etc).can I use this formula to calculate excluding sat &sun..despite the date format are not same?

[@itsagoodday2165]

Can someone help me? I need to know how to bring a value of 0 when my start and end date are same becuz the task started and ended within same day. Please help!

[@owen3009]

Hi Did any one respond? i am also having the same issue. Please let me know if you find any solution

[@mohammadmazhar6184]

Thanks for the nice video. What do it do if I have to get time instead of days... Example: I want to when something has crossed 100 working hours starting from Thursday 7 PM ...if I use workdays it doesn't calculate exact time but go by day taking Thursday as full day although it was not full day instead it was just a few of the the day... Your help will be much appreciated

[@Gchrgr]

What if I want to exclude from the count only the holidays? I mean that all days are working days for my calculation, except for official holidays. Is there another formula that does this? Any other workaround for it? Not to forget: THANKS for the GREAT series of videos!!!!!!!

[@pratikpadariya2945]

It's really very useful for me.. 😊😊

[@soumiamoujane3695]

I Love your videos Leila. They are useful and helpful. Thank you very much.

[@sunilchanda1212]

How to calculate network days when there is alternate Saturday is holiday .I mean (2nd & 4th Saturday + all Sundays are holidays)( 1st, 3rd and 5th saturdays are working)

[@missmarshmallowzz]

Thanks Leila. How to adjust this formula to count Saturday start date or/and end date? For example, if start date is on Friday, end date is on Saturday, how to get the working day as 1 instead of 0?

[@2010sparsh]

How about you use hours instead of days...say you have 5 task of 4. 4. 4. 40. 4 hours

[@DirkOutdoor]

At this point here, Leila, I want to thank you for all the videos I've seen from you until now. And thank you for the very clear English you speak and the perfect explanations so I can follow you without using a dictionary. I'm looking forward to watch all your other videos and be sure: I will watch them ALL! You make my Excel Life more and more interesting. Greetings from Koblenz, Germany and thumb north! 👍👍👍

[@chrissaz6977]

Thanks! But I think using Indirect function with named range probably would be a bit easier.

[@MrGulsaeed]

Hi Leila, if I have hundreds of rows with each row with a different days off so how would I calculate that?

[@royeden1084]

Thanks for the tutorial!! I have one issue , when I add +1 and the start date and end date are the same it is returning 2 instead of 0 ...any idea how can I fix it?? I do not want the start date to be calculated as 1 hence I have added a +1 to the start date

[@foreverrcocochenel]

Very helpful! Thank you. :)

[@chinkonglam4290]

How to insert the calendar onto the worksheet?

[@anujjain5989]

please write date & against the same date please type weekday . Select both cell & scroll down with mouse.

[@Cracktune]

I love you.

[@hosseinhosseinpoor4845]

thanks

[@a.bad.mormon6679]

So, first of all, thank you!! This video really helped me. I only have one question though: I'm having trouble when the start and end date are the same date. For example I started to work on something on January 12, and I ended it the same day. The formula should say 0 days but it still gives me 1 day as a result. Do you know how to solve this? Also, I tried with the "+1" for the start date and the result is now "-2" hahah

[@a.bad.mormon6679]

I just want to say that I figured it out by adding "-1" at the end of the formula. Like this: =NETWORKDAYS(start_date, end_date, [holidays])-1

[@aasirelmutasim3845]

Hi Lelia, I would like to thank you about all informative and helpful tutorial. Can you teach us how we can automatically create shift schedule?!

[@karolinaunderwood1039]

that's great. I am looking for calculating a number of working days from the date range per month. For example, the number of working dates between 01/04/2019 and 15/05/2019 for April and May separately. IS this something that can be done with one formula as well?

[@LeilaGharani]

Yes - you can use the EOMONTH function to get the date for the end of the month and then combine that with the networkday function. You start date is the first date, the end data is the EOMONTH of the start date - you can also use it to get the first day of the next month EOMONTH +1 and this way you can get the days in May too.

[@abarceno]

Hi, Leila. Can I use networkdays for two start dates and two ending dates?

[@KelvinKeahey]

Awesome video. Great style and information. I’m setting the tablet up on my desk so I can easily reference you in a pinch.

[@nargis751]

how can you mofify the weekend to select more than 2 days

[@MrWhisper119]

So wrapped to have come across this video. You’re a life saver!!!

[@tamannathareja4706]

thank you so much. really helpful

[@sanjayshenoy83]

Good one. I do have a query Can I have the dates as = Start date plus duration = End Date (here the end date must exlcude weekend and holidays)

[@LeilaGharani]

Try workday: kzbin.info/www/bejne/bWelg6SAfa1ng80

[@olgafre]

This is the best channel I ever seen for excel help

[@LeilaGharani]

Thank you Olga 😘

[@Shankar_Sharma_Official]

Nice

[@yusufhamzaahmed735]

I tried to follow your example but got an error each time. #VALUE!.

[@LeilaGharani]

Try downloading the workbook (link is in the description of the video) and compare with your solution.

[@hernanperez912]

I just made my day.. Thanks

[@UbaidUllah-nj5et]

very nice explanation mam....mam i requested you for releative and absolute cell referencing video a month ago....it would be really nice of u mam if u upload a video on the aforesaid area...

[@LeilaGharani]

You're right. And it's on my list. After this week, I'll have some VBA videos coming in, but I'll make sure to record this one soon. Thanks for the reminder :)

[@UbaidUllah-nj5et]

Welcome n thanks mam...waoo vba...despeeately waiting for vba videos mam...

[@mohammadasad9890]

xlent 👌👌👌👌

[@mohammadasad9890]

leila i have ur emaile​ id

[@MAJIDMANSOOR1]

Hmmm Good one Miss i will Practice of this formula thank you

[@LeilaGharani]

You're welcome Majid.

[@HoppiHopp]

Excellent video. Great preparation and execution.

[@LeilaGharani]

Very happy to hear that. Thanks for your comment!

[@excelcomputer1847]

your method of teaching is good and reasonable and I got a lot of new style of excel. I want to get knowledge about macro from beginning because my concept about macro is not clear

[@LeilaGharani]

Glad to hear that. I am planning to add some videos on that in the near future....

[@billostrove2016]

Leila Gharani that would be great. I'd love to see videos on macros!

[@ckokse]

Great! Tutorials about macros would definitely be a big help!

[@ismailismaili0071]

Sounds great

[@LeilaGharani]

👍

[@mohammadasad9890]

Thanks leila

[@LeilaGharani]

You're welcome Mohammad.