Dear Friends,
In my file I want to count the total working days of the month of May 2013. There are 31 days in May and 4 sundays and 1 bank holiday that means total 5 holidays. 31-5 = 26. That means there are 26 working days in May but my formula shows 23 working days which is wrong. I don't have a 5 days week.
How can I get the correct answer 26 with a formula because manually it's not possible every time.
Solution please!
Thanking you in anticipation.
Mukesh
Hi mukeshbaviskar
Are you using Excel 2007 as per your profile! If you are using 2010 look at the NETWORKDAYS.INTL
Regards Kevin
Merged Cells (They are the work of the devil!!!)
Hello Kevin,
Nice to see you again. I'm using m. s. office 2007 and I want to solve my problem with it only. I don't have m. s. office 2010.
Waiting for solution.
Thanking you in anticipation.
Mukesh
One way..
=(B2+1-A2)-COUNTIF(G1:G10,">0")
Format as general.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Hi,
See attached file of another way!
Hello Fotis1991,
Thank you it's working fine but if you know I would like to know the logic behind networkdays function please.
Ok, Thank you for solution.
Mukesh
Hello duanzhuanming,
Thank you. In your formula if the list of holidays increases then the result is wrong. How to add the list of holidays range to the formula? The formla sounds little difficult to understand.
Thank you.
Mukesh
Hi,Originally Posted by mukeshbaviskar
if list of holidays increases, you must change holidays data region. in that formula: only a holiday-->G1
eg. holidays data region: G1:G9----> formula:=NETWORKDAYS(A2,B2,G1)+IF(WEEKDAY(B2)=7,WEEKNUM(B2)+1,WEEKNUM(B2))-WEEKNUM(A2)
with my formula, you can calculate nos of days working between two dates ( eg 1/5/2013->15/5/2013)....=NETWORKDAYS(A2,B2,G1:G9)+IF(WEEKDAY(B2)=7,WEEKNUM(B2)+1,WEEKNUM(B2))-WEEKNUM(A2)
Hello duanzhuanming,
Thank you. Now it's working fine. It's a very tricky formula.
Now my problem is solved.
Thank you.
Mukesh
This formula will give you the number of working days between any two dates...
=NETWORKDAYS(A2,B2,holidays)+INT((WEEKDAY(A2)+B2-A2)/7)
.....but like duanzhuanming's suggestion that won't cope with any Saturday holidays, to do that use this version
=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)))>1)*(COUNTIF(holidays,ROW(INDIRECT(A2&":"&B2)))=0))
where holidays is a range containing holiday dates
Audere est facere
Good morning daddylonglegs,
Thank you for formulas. It's a good collection of network days. It's working fine.
Thank you.
Mukesh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks