Time elapsed

[franalph]

I am trying to include holidays into the following formula networkdays("1/10/2012",TODAY())can anyone help. It is a time elapsed formula but I need to exclude some dates which are week days, I have tried various things but cant crack it.

 

[koi]

Hi,


put all the holiday list date from A1 : A10 then press CTRL+F3, create new name range... you can name it as "holiday" for example (without quote)


then in the refers to : =Sheet1!$A$1:$A$10


then do networkdays("1/10/2012",TODAY(),[holiday]) and it will work


after you type ,today(), then just type holi...and press tab then enter

 

[Luke M]

Your other problem is your start date. By using quotation marks, XL will treat it as a string. Best case would be to put the date in a cell and reference the cell, or use the DATE function.

=NETWORKDAYS(B2,TODAY(),A1:A10)


where A1:A10 is your list of holidays.

 

[franalph]

Thanks, that worked. Although I did not forsee another problem. I am creating a template that for KPI where when people open it they can see where they are within a quarter. So the problem I have is that because I am not putting the date in or a ref cell for the finish date it will carry on changing after the quater. I do not know where to put the finish date/cell.


So if I explain what I am doing might make it easier. So Jan 1st to March 31st I want the guys to be able to open this work sheet and it tell them where they are to date (I have other cells with formulas using this date to turn into a percentage). Then within the workbook they would have a sheet for each quarter. The problem I have is that with the formula as I have it the sheet carries on calculating after the quarter has finished.

 

[Luke M]

You could you a MIN function to set a cutoff point, like:

=NETOWKRDAYS(B2,MIN(LastDayOfQtr,TODAY()),A1:A10)

 

[franalph]

Thanks Luke, I have used that, hopefully that will do the job.