MrExcel Publishing
Your One Stop for Excel Tips & Solutions

networkdays function


Posted by Mike Collard on August 25, 2001 11:39 AM

I am using the function networkdays as part of a formula. Networkdays calculates the number of work days between two given dates. I would like the function to automatically use the end date already listed in another column, but I can't seem to get it to work. How can I get this link established, so I don't have to manually change the end date in every row?


Posted by Aaldin Akyurek on August 25, 2001 3:31 PM

Post the formula that you use and the cell ref of your end date or end dates.

Posted by Mike Collard on August 26, 2001 8:16 PM

Here is the formula: =H124*(F124-(NETWORKDAYS("01/01/02","06/01/02")/261*(F124-E124)))
This is an example from row 124 of my sheet.
The formula is used in column I, while the end date, 06/01/02 in this example, is already in column G, in this case cell G124. The beginning date of 01/01/02 is fixed for all rows.
Any help would be appreciated.

Posted by Aladin Akyurek on August 26, 2001 11:07 PM

Mike,

It looks like both dates are fixed from your description. If so,

(NETWORKDAYS("01/01/02","06/01/02")/261 is a constant for the calculations that you make in I. If so, put this part in a cell of its own, say, in E124:

=NETWORKDAYS("01/01/02",$G$124)/261

then change the formula to:

=H124*(F124-$E$124*(F124-E124)))

If 01/01/02 is fixed and you have a bunch of end dates in column G from G124 on, then I'd put "01/01/02" (although not strictly necessary) in say E124 and change the formula to:

=H124*(F124-(NETWORKDAYS($E$124,G124)/261*(F124-E124))) [ copy down this as far as needed in column I ]

Aladin

==============

Posted by Mike Collard on August 27, 2001 11:28 AM

Posted by Mike Collard on August 27, 2001 11:31 AM

Thanks for your help, Aladin. When I first saw your answer, I thought it was the same thing I had already tried, but then I realized that my mistake was that I had put the cell references in quotes, such as NETWORKDAYS("$E$124","G124").