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

[@olgafre]

This is the best channel I ever seen for excel help

[@LeilaGharani]

Thank you Olga 😘

[@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! 👍👍👍

[@soumiamoujane3695]

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

[@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.

[@MrWhisper119]

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

[@pratikpadariya2945]

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

[@tamannathareja4706]

thank you so much. really helpful

[@HoppiHopp]

Excellent video. Great preparation and execution.

[@LeilaGharani]

Very happy to hear that. Thanks for your comment!

[@foreverrcocochenel]

Very helpful! Thank you. :)

[@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.

[@hernanperez912]

I just made my day.. Thanks

[@hosseinhosseinpoor4845]

thanks

[@Cracktune]

I love you.

[@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?

[@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!

[@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 :)

[@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

[@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?!

[@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?

[@tazrianalamjitu]

How to insert this calendar in sheet

[@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

[@abarceno]

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

[@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!!!!!!!

[@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?

[@MAJIDMANSOOR1]

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

[@LeilaGharani]

You're welcome Majid.

[@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

[@mohammadasad9890]

Thanks leila

[@LeilaGharani]

You're welcome Mohammad.

[@ismailismaili0071]

Sounds great

[@LeilaGharani]

👍

[@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.

[@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

[@chrissaz6977]

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

[@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

[@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.

[@nargis751]

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

[@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

[@amrelgammal2353]

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

[@Shankar_Sharma_Official]

Nice

[@mohammadasad9890]

xlent 👌👌👌👌

[@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...

[@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.

[@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)

[@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.

[@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?

[@2010sparsh]

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

[@Franceskineos]

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

[@mohammadasad9890]

leila i have ur emaile​ id