Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Difference
Hello,
I have a forumla from the help that allows me to calculate the difference between two dates without weekends which is =NETWORKDAYS(D22,E22) and works great. However, I have two questions: 1. How do I get it to start from the next day instead of include the first date? i.e. 12/6/2007 - 12/12/2007 should be 4 workdays. I don't want to include 12/6/2007. 2. What if another user opens this spreadsheet and doesn't have the tool pak installed. Will the date formula still work? I ask because I had to install the tool pak on my computer. Thanks!!!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Difference
To answer question 1. =NETWORKDAYS(D22,E22)-1
To answer question 2. NETWORKDAYS requires the Analysis Toolpak to be installed. If it's not, the user will get a #NAME? error as Excel will not recognize the function. Tyro "Stockwell43" wrote in message ... Hello, I have a forumla from the help that allows me to calculate the difference between two dates without weekends which is =NETWORKDAYS(D22,E22) and works great. However, I have two questions: 1. How do I get it to start from the next day instead of include the first date? i.e. 12/6/2007 - 12/12/2007 should be 4 workdays. I don't want to include 12/6/2007. 2. What if another user opens this spreadsheet and doesn't have the tool pak installed. Will the date formula still work? I ask because I had to install the tool pak on my computer. Thanks!!!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Difference
Hi Tyro, thank you for your reply.
Ok, I got number and works perfectly! For number two, how can I make number work correctly without the toolpak? I hate to take a chase on sending this to people and they can't obtain the information. Any suggestions? Thanks!! "Tyro" wrote: To answer question 1. =NETWORKDAYS(D22,E22)-1 To answer question 2. NETWORKDAYS requires the Analysis Toolpak to be installed. If it's not, the user will get a #NAME? error as Excel will not recognize the function. Tyro "Stockwell43" wrote in message ... Hello, I have a forumla from the help that allows me to calculate the difference between two dates without weekends which is =NETWORKDAYS(D22,E22) and works great. However, I have two questions: 1. How do I get it to start from the next day instead of include the first date? i.e. 12/6/2007 - 12/12/2007 should be 4 workdays. I don't want to include 12/6/2007. 2. What if another user opens this spreadsheet and doesn't have the tool pak installed. Will the date formula still work? I ask because I had to install the tool pak on my computer. Thanks!!!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Difference
Well, if they are using Excel 2007, NETWORKDAYS is included without having
to install the Analysis Toolpak which is required only for earlier versions. NETWORKDAYS also allows you to specify a range of holidays to exclude. I don't know anyway to get what the function provides without having to write VBA code to accomplish what the function does. The VBA function would have to calculate the dates differences taking weekends and holidays into account. Perhaps a workaround exists, but I don't know of it. Tyro "Stockwell43" wrote in message ... Hi Tyro, thank you for your reply. Ok, I got number and works perfectly! For number two, how can I make number work correctly without the toolpak? I hate to take a chase on sending this to people and they can't obtain the information. Any suggestions? Thanks!! "Tyro" wrote: To answer question 1. =NETWORKDAYS(D22,E22)-1 To answer question 2. NETWORKDAYS requires the Analysis Toolpak to be installed. If it's not, the user will get a #NAME? error as Excel will not recognize the function. Tyro "Stockwell43" wrote in message ... Hello, I have a forumla from the help that allows me to calculate the difference between two dates without weekends which is =NETWORKDAYS(D22,E22) and works great. However, I have two questions: 1. How do I get it to start from the next day instead of include the first date? i.e. 12/6/2007 - 12/12/2007 should be 4 workdays. I don't want to include 12/6/2007. 2. What if another user opens this spreadsheet and doesn't have the tool pak installed. Will the date formula still work? I ask because I had to install the tool pak on my computer. Thanks!!!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Difference
I think this non-ATP formula will do the same thing for #2...
=E22-D22-SUMPRODUCT(--(WEEKDAY(DATE(YEAR(D22),MONTH(D22),DAY(D22)+ROW(IN DIRECT("1:"&(E22-D22)))),2)5)) Rick "Stockwell43" wrote in message ... Hi Tyro, thank you for your reply. Ok, I got number and works perfectly! For number two, how can I make number work correctly without the toolpak? I hate to take a chase on sending this to people and they can't obtain the information. Any suggestions? Thanks!! "Tyro" wrote: To answer question 1. =NETWORKDAYS(D22,E22)-1 To answer question 2. NETWORKDAYS requires the Analysis Toolpak to be installed. If it's not, the user will get a #NAME? error as Excel will not recognize the function. Tyro "Stockwell43" wrote in message ... Hello, I have a forumla from the help that allows me to calculate the difference between two dates without weekends which is =NETWORKDAYS(D22,E22) and works great. However, I have two questions: 1. How do I get it to start from the next day instead of include the first date? i.e. 12/6/2007 - 12/12/2007 should be 4 workdays. I don't want to include 12/6/2007. 2. What if another user opens this spreadsheet and doesn't have the tool pak installed. Will the date formula still work? I ask because I had to install the tool pak on my computer. Thanks!!!!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Difference
How does that handle holidays?
Tyro "Rick Rothstein (MVP - VB)" wrote in message ... I think this non-ATP formula will do the same thing for #2... =E22-D22-SUMPRODUCT(--(WEEKDAY(DATE(YEAR(D22),MONTH(D22),DAY(D22)+ROW(IN DIRECT("1:"&(E22-D22)))),2)5)) Rick "Stockwell43" wrote in message ... Hi Tyro, thank you for your reply. Ok, I got number and works perfectly! For number two, how can I make number work correctly without the toolpak? I hate to take a chase on sending this to people and they can't obtain the information. Any suggestions? Thanks!! "Tyro" wrote: To answer question 1. =NETWORKDAYS(D22,E22)-1 To answer question 2. NETWORKDAYS requires the Analysis Toolpak to be installed. If it's not, the user will get a #NAME? error as Excel will not recognize the function. Tyro "Stockwell43" wrote in message ... Hello, I have a forumla from the help that allows me to calculate the difference between two dates without weekends which is =NETWORKDAYS(D22,E22) and works great. However, I have two questions: 1. How do I get it to start from the next day instead of include the first date? i.e. 12/6/2007 - 12/12/2007 should be 4 workdays. I don't want to include 12/6/2007. 2. What if another user opens this spreadsheet and doesn't have the tool pak installed. Will the date formula still work? I ask because I had to install the tool pak on my computer. Thanks!!!!! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Difference
The same way this formula does...
=NETWORKDAYS(D22,E22) which is the formula that the OP originally posted and said works great. Rick "Tyro" wrote in message et... How does that handle holidays? Tyro "Rick Rothstein (MVP - VB)" wrote in message ... I think this non-ATP formula will do the same thing for #2... =E22-D22-SUMPRODUCT(--(WEEKDAY(DATE(YEAR(D22),MONTH(D22),DAY(D22)+ROW(IN DIRECT("1:"&(E22-D22)))),2)5)) Rick "Stockwell43" wrote in message ... Hi Tyro, thank you for your reply. Ok, I got number and works perfectly! For number two, how can I make number work correctly without the toolpak? I hate to take a chase on sending this to people and they can't obtain the information. Any suggestions? Thanks!! "Tyro" wrote: To answer question 1. =NETWORKDAYS(D22,E22)-1 To answer question 2. NETWORKDAYS requires the Analysis Toolpak to be installed. If it's not, the user will get a #NAME? error as Excel will not recognize the function. Tyro "Stockwell43" wrote in message ... Hello, I have a forumla from the help that allows me to calculate the difference between two dates without weekends which is =NETWORKDAYS(D22,E22) and works great. However, I have two questions: 1. How do I get it to start from the next day instead of include the first date? i.e. 12/6/2007 - 12/12/2007 should be 4 workdays. I don't want to include 12/6/2007. 2. What if another user opens this spreadsheet and doesn't have the tool pak installed. Will the date formula still work? I ask because I had to install the tool pak on my computer. Thanks!!!!! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Difference
Perhaps the OP doesn't care about holidays. But that makes the OP's
NETWORKDAYS inaccurate. Holidays are important. I once worked for a university that closed for two weeks spanning Christmas and New Year's. All employees got paid for those two weeks as paid holidays in addtion to the regular holidays. The NETWORKDAYS for that two week period was zero. To be accurate NETWORKDAYS has to take every holiday into account. Regards, Tyro "Rick Rothstein (MVP - VB)" wrote in message ... The same way this formula does... =NETWORKDAYS(D22,E22) which is the formula that the OP originally posted and said works great. Rick "Tyro" wrote in message et... How does that handle holidays? Tyro "Rick Rothstein (MVP - VB)" wrote in message ... I think this non-ATP formula will do the same thing for #2... =E22-D22-SUMPRODUCT(--(WEEKDAY(DATE(YEAR(D22),MONTH(D22),DAY(D22)+ROW(IN DIRECT("1:"&(E22-D22)))),2)5)) Rick "Stockwell43" wrote in message ... Hi Tyro, thank you for your reply. Ok, I got number and works perfectly! For number two, how can I make number work correctly without the toolpak? I hate to take a chase on sending this to people and they can't obtain the information. Any suggestions? Thanks!! "Tyro" wrote: To answer question 1. =NETWORKDAYS(D22,E22)-1 To answer question 2. NETWORKDAYS requires the Analysis Toolpak to be installed. If it's not, the user will get a #NAME? error as Excel will not recognize the function. Tyro "Stockwell43" wrote in message ... Hello, I have a forumla from the help that allows me to calculate the difference between two dates without weekends which is =NETWORKDAYS(D22,E22) and works great. However, I have two questions: 1. How do I get it to start from the next day instead of include the first date? i.e. 12/6/2007 - 12/12/2007 should be 4 workdays. I don't want to include 12/6/2007. 2. What if another user opens this spreadsheet and doesn't have the tool pak installed. Will the date formula still work? I ask because I had to install the tool pak on my computer. Thanks!!!!! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Difference
Okay, although I'll still note that holidays are optional in NETWORKDAYS...
assuming the holiday dates are listed in H1:H20 (whether there are 20 listed holidays or not)... =E22-D22-SUMPRODUCT(--(WEEKDAY(DATE(YEAR(D22),MONTH(D22),DAY(D22)+ROW(IN DIRECT("1:"&(E22-D22)))),2)5))-SUMPRODUCT((D22<=H1:H20)*(E22=H1:H20)) where the last SUMPRODUCT is the optional holidays list. Rick "Tyro" wrote in message et... Perhaps the OP doesn't care about holidays. But that makes the OP's NETWORKDAYS inaccurate. Holidays are important. I once worked for a university that closed for two weeks spanning Christmas and New Year's. All employees got paid for those two weeks as paid holidays in addtion to the regular holidays. The NETWORKDAYS for that two week period was zero. To be accurate NETWORKDAYS has to take every holiday into account. Regards, Tyro "Rick Rothstein (MVP - VB)" wrote in message ... The same way this formula does... =NETWORKDAYS(D22,E22) which is the formula that the OP originally posted and said works great. Rick "Tyro" wrote in message et... How does that handle holidays? Tyro "Rick Rothstein (MVP - VB)" wrote in message ... I think this non-ATP formula will do the same thing for #2... =E22-D22-SUMPRODUCT(--(WEEKDAY(DATE(YEAR(D22),MONTH(D22),DAY(D22)+ROW(IN DIRECT("1:"&(E22-D22)))),2)5)) Rick "Stockwell43" wrote in message ... Hi Tyro, thank you for your reply. Ok, I got number and works perfectly! For number two, how can I make number work correctly without the toolpak? I hate to take a chase on sending this to people and they can't obtain the information. Any suggestions? Thanks!! "Tyro" wrote: To answer question 1. =NETWORKDAYS(D22,E22)-1 To answer question 2. NETWORKDAYS requires the Analysis Toolpak to be installed. If it's not, the user will get a #NAME? error as Excel will not recognize the function. Tyro "Stockwell43" wrote in message ... Hello, I have a forumla from the help that allows me to calculate the difference between two dates without weekends which is =NETWORKDAYS(D22,E22) and works great. However, I have two questions: 1. How do I get it to start from the next day instead of include the first date? i.e. 12/6/2007 - 12/12/2007 should be 4 workdays. I don't want to include 12/6/2007. 2. What if another user opens this spreadsheet and doesn't have the tool pak installed. Will the date formula still work? I ask because I had to install the tool pak on my computer. Thanks!!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date difference = zero? | Excel Discussion (Misc queries) | |||
Calculating Difference Between Start Date & Time And End Date & Ti | Excel Discussion (Misc queries) | |||
difference two date | Excel Discussion (Misc queries) | |||
Date difference | Excel Worksheet Functions | |||
Difference of date | New Users to Excel |