Dear Sir
i would like to autofill between two date and calculate working day according to with looking for holiday
see my example
when i enter c4 Start date and C5 end date they calculate and fill between this in column c with regarding to holiday which is between F:Cl
our weekend is Friday and Saturday
Do you actually need it to auto-fill? Or will calculating the number of working days be sufficient?
You can probably tweak the =networkdays.intl() function to work if you don't need auto-fill.
If you do need to auto-fill, I'm not totally sure how to go about making a formula that just drags down, sorry.
Last edited by TheN; 07-30-2016 at 03:18 PM.
What is wrong with your current formula? and dragging it down?
You need to change C4 but leave C5 onwards as they are
=IF($B$4<$B$5,WORKDAY.INTL($B$4,1,7,$F$4:$F$20),"")
But I need to add this formula selection automatic holiday
Meaning if I 2016 add holiday 2016 if 2017 add holiday 2017 and soon
And if I select from 2016-2017 take two holiday
These match the year in B4 versus your column header years to select the holidays. Change the 30 if more than 30 days holiday.
In C4
=IF($B$4<$B$5,WORKDAY.INTL($B$4,1,7,OFFSET($A$4,,MATCH(YEAR($B$4),$A$3:$CL$3,0),30)),"")
in C5 and copy down
=IF(C4+1<$B$5,WORKDAY.INTL($C4,1,7,OFFSET($A$4,,MATCH(YEAR($B$4),$A$3:$CL$3,0)-1,30,1)),"")
For holidays, the array has to be a single dimension so if you want to have a year which starts in 2016 and ends in 2017 then (as far as I can ascertain) the holidays need to be in a singe (column) list.
Planning to 2100 is very optimistic !!!.
Last edited by JohnTopley; 07-31-2016 at 03:35 AM.
Certainly for me!!!!Originally Posted by JohnTopley
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
@Glenn,
Et moi aussi !
Welcome back!
please knidly find attached file that give me error after change the date for 2017 , annd also i try other date but give me error
regarding to planing for 2100 because i make program that i need to make it unlimited that can any one edit holiday in future and applicable
Don't have blanks in your holiday list: start in row 4.
still give me error as in attached file
Put this C4
=IF($B$4<$B$5,WORKDAY.INTL($B$4,1,7,OFFSET($A$4,,MATCH(YEAR($B$4),$A$3:$CL$3,0)-1,30,1)),"")
i would like to know why if leave holiday empty not calculate , suppose no holiday , why not give all working day without holidy
See attached: I discovered the VALUE errors appeared to be caused by "error" cells in 018 onwards in the files you sent.
and my formula In B4 was incorrect. although it worked if "018 onwards data was truly blank.
how can leave the cell blank instead of #Value
The last file I sent works OK: that is why I sent it! I did "Clear Contents" on all cells.
Yes I know but when I drag the function after finish give me #value
Because I want to make this function in all Column
i mean after when i drag the function for extra cell it give me #value
also can see my new attached file for another function that i want
according to my selection in sampl sheet in cell A2 which divide month to four week and selec month and year
that will create my plan automatic In E3 :I22
with rules that i write in column M
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks