A
Anonymous
How can I calculate the number of business days between two dates? I tried
using NETWORKDAYS but the answer comes up 0.
using NETWORKDAYS but the answer comes up 0.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
How can I calculate the number of business days between two dates
R
Ron Rosenfeld
How can I calculate the number of business days between two dates? I tried
using NETWORKDAYS but the answer comes up 0.
Look at HELP for NETWORKDAYS.
If you are using it correctly, there is something wrong with your data.
For example, if you have the transition formula options set, and you are
entering the dates as text and not values, you can get a zero result. This is
because, under those circumstances, the value you enter gets interpreted as a
fractional number and not a date.
Easiest fix would be to deselect the Lotus transition options, and then enter
the dates in fields not formatted as text.
--ron
A
Andrew Ball
Not sure I understood Ron's reply, but try this.
1) In cell A1 enter the first date in dd/mm/yyyy format
2) In cell B1 enter the second date also in dd/mm/yyyy format
3) In cell C1 enter "=NETWORKDAYS(I12,J12,Holidays!$B$2:$B$15)-1" without
the quotes. Either cut & paste, or copy it VERY carefully, one tiny mistake
and you're scuppered!
4) Rename the next worksheet "Holiday" without the quotes or the exclamation
mark you can see in the formula
5) List all the known business holidays (Easter, Xmas, etc, in cells B2 to
B15 on the holiday worksheet, if you have more or less business holidays than
we do, amend the B15 figure in step 3 accordingly.
6) The "-1" in the formula in step 3 stops Excel counting both the first and
the second dates as part of the answer, eg 17/06/2008 to 18/06/2008 will give
the answer "2" as it counts both the 17th and the 18th, but I needed it to
calculate the answer as one business day later, hence the need for the "-1".
Hope that helps, took me ages the first time, the Excel help function wasn't
the best on the subject
Andrew
1) In cell A1 enter the first date in dd/mm/yyyy format
2) In cell B1 enter the second date also in dd/mm/yyyy format
3) In cell C1 enter "=NETWORKDAYS(I12,J12,Holidays!$B$2:$B$15)-1" without
the quotes. Either cut & paste, or copy it VERY carefully, one tiny mistake
and you're scuppered!
4) Rename the next worksheet "Holiday" without the quotes or the exclamation
mark you can see in the formula
5) List all the known business holidays (Easter, Xmas, etc, in cells B2 to
B15 on the holiday worksheet, if you have more or less business holidays than
we do, amend the B15 figure in step 3 accordingly.
6) The "-1" in the formula in step 3 stops Excel counting both the first and
the second dates as part of the answer, eg 17/06/2008 to 18/06/2008 will give
the answer "2" as it counts both the 17th and the 18th, but I needed it to
calculate the answer as one business day later, hence the need for the "-1".
Hope that helps, took me ages the first time, the Excel help function wasn't
the best on the subject
Andrew
A
Andrew Ball
And if you believe everything I say the first time round you'll be scuppered
too!
The formula in Step 3 should have read
"=NETWORKDAYS(A1,B1,Holidays!$B$2:$B$15)-1"
Sorry about that, I cut and pasted it straight out of one of my spread
sheets where the dates happened to be in cells "I12" and "J12".
Andrew
too!
The formula in Step 3 should have read
"=NETWORKDAYS(A1,B1,Holidays!$B$2:$B$15)-1"
Sorry about that, I cut and pasted it straight out of one of my spread
sheets where the dates happened to be in cells "I12" and "J12".
Andrew