Hello Guys,
I am currently using a formula to count workingdays between two dates which includes Saturday also (there some more conditions are there in formula).
My need is :
1. I want to exclude the holiday list also in the existing formula (used in Column E).
2. If possible can i want to have a function that if value in Column D is "Clear" then it freeze the value in Column E (means stop the formula to work).
The sasmple file is attached for your reference.
Thanks,
MG
Please try these functions.
This function will exclude the weekends & holidays.
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(B5),MONTH(B5),DAY(B5))&":"&DATE(YEAR($E$2),MONTH($E$2),DAY(E2)))))<>7),--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(B5),MONTH(B5),DAY(B5))&":"&DATE(YEAR($E$2),MONTH($E$2),DAY(E2)))))<>1),--(COUNTIF('Holiday List'!$A$2:$A$11,ROW(INDIRECT(DATE(YEAR(B5),MONTH(B5),DAY(B5))&":"&DATE(YEAR($E$2),MONTH($E$2),DAY(E2)))))<>1))
or
=NETWORKDAYS(B5,$E$2,'Holiday List'!$A$2:$A$11)
Last edited by ramananhrm; 11-08-2013 at 03:02 AM.
Please click 'Add reputation', if my answer helped you.
Hi Raman,
Thanks for the reply. The SUMPRODUCT formula is working good as it is including Monday to Saturady as an Working day and excluding only Sundays & Holidays.
If you can help me, in my sheet the formula which i am using is having a 1 IF condition for Column C also. Can you merge this formula with that IF condition.
Second, is my above mentione 2nd reuirement (to freeze the cell) is also possible.
Thanks,
MG
Hi Manish,
Please try this updated file.
Last edited by ramananhrm; 11-08-2013 at 05:08 AM. Reason: If condition is included
Hi Raman,
The formula with IF condition is working perfect. Just last query is there any possibilites regarding of my 2nd requirement which is:
2. If possible, i want to have a function that if value in Column D is "Clear" then it freeze the value in Column E (means stop the formula to count further).
Many Thanks for help.
Last edited by Manish_Gupta; 11-08-2013 at 05:22 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks