If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I calculate the number of business days between two dates
How can I calculate the number of business days between two dates? I tried
using NETWORKDAYS but the answer comes up 0. -- Jean |
#2
|
|||
|
|||
How can I calculate the number of business days between two dates
On Wed, 18 Jun 2008 05:48:01 -0700, Anonymous
wrote: 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 |
#3
|
|||
|
|||
How can I calculate the number of business days between two dates
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 "Anonymous" wrote: How can I calculate the number of business days between two dates? I tried using NETWORKDAYS but the answer comes up 0. -- Jean |
#4
|
|||
|
|||
How can I calculate the number of business days between two da
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 "Andrew Ball" wrote: 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 "Anonymous" wrote: How can I calculate the number of business days between two dates? I tried using NETWORKDAYS but the answer comes up 0. -- Jean |
Thread Tools | |
Display Modes | |
|
|