Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Networkdays
Hello all,
I am trying to create a formlua that will tell me a due date ten business days from the initial date. I currently have a formula that will give me the due date based on 24hrs from the initial date (business days) but I also need one to expend out ten business days can you help me modify this formula to give me a due date ten business days from an initial date...note** this formula excludes weekends... =IF(NETWORKDAYS(D2,D2+1)<2,D2+3,D2+1) Thank you |
#2
|
|||
|
|||
Hi
use the WORKDAY function =WORKDAY(A1,10) where A1 is the initial date and 10 is the number of working days to add to the date (this formula also excludes sat & sun) - you can also exclude holidays in the third parameter - check out help for details. Note: you need the analysis tool-pak installed to use this formula Cheers JulieD "Dmorri254" wrote in message ... Hello all, I am trying to create a formlua that will tell me a due date ten business days from the initial date. I currently have a formula that will give me the due date based on 24hrs from the initial date (business days) but I also need one to expend out ten business days can you help me modify this formula to give me a due date ten business days from an initial date...note** this formula excludes weekends... =IF(NETWORKDAYS(D2,D2+1)<2,D2+3,D2+1) Thank you |
#3
|
|||
|
|||
Thanks much Julie...that did work...can you help me with this one? First here
is a sample: D2 = initial date E2 = Due date 48hrs Tue, Jan 18/05,5:00 PM Thu, Jan 20/05,5:00 PM L2 = Due date 10 days from initial date Tue, Feb 01/05,12:00 AM Typically info is sent out after 5pm this gives one day to recieve and review hence 48hrs due date..okay lets say it goes out on Thursday..the due date needs to be Monday..also, on Friday, the due date needs to be Tuesday...How do I modify to this to be correct =IF(NETWORKDAYS(D5,D5+2)<2,D5+3,D5+2) Second...how do I modify the workday function in L2 to also give me the time when it is due? Thanx again "JulieD" wrote: Hi use the WORKDAY function =WORKDAY(A1,10) where A1 is the initial date and 10 is the number of working days to add to the date (this formula also excludes sat & sun) - you can also exclude holidays in the third parameter - check out help for details. Note: you need the analysis tool-pak installed to use this formula Cheers JulieD "Dmorri254" wrote in message ... Hello all, I am trying to create a formlua that will tell me a due date ten business days from the initial date. I currently have a formula that will give me the due date based on 24hrs from the initial date (business days) but I also need one to expend out ten business days can you help me modify this formula to give me a due date ten business days from an initial date...note** this formula excludes weekends... =IF(NETWORKDAYS(D2,D2+1)<2,D2+3,D2+1) Thank you |
#4
|
|||
|
|||
Hi
a couple of things, firstly in E2 i would us the formula =WORKDAY(D3,2) which will give you the date 2 days (48hrs) after the initial date, excluding weekends to put the time in the E3 formula use =WORKDAY(D3,2)+MOD(D3,24) and for L3, use the formula =WORKDAY(D3,10)+MOD(D3,24) Cheers JulieD "Dmorri254" wrote in message ... Thanks much Julie...that did work...can you help me with this one? First here is a sample: D2 = initial date E2 = Due date 48hrs Tue, Jan 18/05,5:00 PM Thu, Jan 20/05,5:00 PM L2 = Due date 10 days from initial date Tue, Feb 01/05,12:00 AM Typically info is sent out after 5pm this gives one day to recieve and review hence 48hrs due date..okay lets say it goes out on Thursday..the due date needs to be Monday..also, on Friday, the due date needs to be Tuesday...How do I modify to this to be correct =IF(NETWORKDAYS(D5,D5+2)<2,D5+3,D5+2) Second...how do I modify the workday function in L2 to also give me the time when it is due? Thanx again "JulieD" wrote: Hi use the WORKDAY function =WORKDAY(A1,10) where A1 is the initial date and 10 is the number of working days to add to the date (this formula also excludes sat & sun) - you can also exclude holidays in the third parameter - check out help for details. Note: you need the analysis tool-pak installed to use this formula Cheers JulieD "Dmorri254" wrote in message ... Hello all, I am trying to create a formlua that will tell me a due date ten business days from the initial date. I currently have a formula that will give me the due date based on 24hrs from the initial date (business days) but I also need one to expend out ten business days can you help me modify this formula to give me a due date ten business days from an initial date...note** this formula excludes weekends... =IF(NETWORKDAYS(D2,D2+1)<2,D2+3,D2+1) Thank you |
#5
|
|||
|
|||
This did not work what did I do wrong?? Here is a sample of what was returned
using your advice each excludes the jan17th holiday D2 = initial date E2 = should be 48hrs from D2 (1/18/05 5:00PM) Fri, Jan 14/05,5:00 PM returned Wed, Feb 02/05,5:00 PM L2 = should be 10days from D2 (1/31/05 5:00pm) returned Fri, Feb 14/05,5:00 PM "JulieD" wrote: Hi a couple of things, firstly in E2 i would us the formula =WORKDAY(D3,2) which will give you the date 2 days (48hrs) after the initial date, excluding weekends to put the time in the E3 formula use =WORKDAY(D3,2)+MOD(D3,24) and for L3, use the formula =WORKDAY(D3,10)+MOD(D3,24) Cheers JulieD "Dmorri254" wrote in message ... Thanks much Julie...that did work...can you help me with this one? First here is a sample: D2 = initial date E2 = Due date 48hrs Tue, Jan 18/05,5:00 PM Thu, Jan 20/05,5:00 PM L2 = Due date 10 days from initial date Tue, Feb 01/05,12:00 AM Typically info is sent out after 5pm this gives one day to recieve and review hence 48hrs due date..okay lets say it goes out on Thursday..the due date needs to be Monday..also, on Friday, the due date needs to be Tuesday...How do I modify to this to be correct =IF(NETWORKDAYS(D5,D5+2)<2,D5+3,D5+2) Second...how do I modify the workday function in L2 to also give me the time when it is due? Thanx again "JulieD" wrote: Hi use the WORKDAY function =WORKDAY(A1,10) where A1 is the initial date and 10 is the number of working days to add to the date (this formula also excludes sat & sun) - you can also exclude holidays in the third parameter - check out help for details. Note: you need the analysis tool-pak installed to use this formula Cheers JulieD "Dmorri254" wrote in message ... Hello all, I am trying to create a formlua that will tell me a due date ten business days from the initial date. I currently have a formula that will give me the due date based on 24hrs from the initial date (business days) but I also need one to expend out ten business days can you help me modify this formula to give me a due date ten business days from an initial date...note** this formula excludes weekends... =IF(NETWORKDAYS(D2,D2+1)<2,D2+3,D2+1) Thank you |
#6
|
|||
|
|||
Hi
is the time always 5pm if so change the formulas to E2: =WORKDAY(D3,2,H3)+(17/24) L2: =WORKDAY(D3,10,H3)+(17/24) (the H3 refered to in the formula contains the date of the holiday, adjust as necessary) - if it's not always 5pm or might not be that time in the future, please let me know Cheers JulieD "Dmorri254" wrote in message ... This did not work what did I do wrong?? Here is a sample of what was returned using your advice each excludes the jan17th holiday D2 = initial date E2 = should be 48hrs from D2 (1/18/05 5:00PM) Fri, Jan 14/05,5:00 PM returned Wed, Feb 02/05,5:00 PM L2 = should be 10days from D2 (1/31/05 5:00pm) returned Fri, Feb 14/05,5:00 PM "JulieD" wrote: Hi a couple of things, firstly in E2 i would us the formula =WORKDAY(D3,2) which will give you the date 2 days (48hrs) after the initial date, excluding weekends to put the time in the E3 formula use =WORKDAY(D3,2)+MOD(D3,24) and for L3, use the formula =WORKDAY(D3,10)+MOD(D3,24) Cheers JulieD "Dmorri254" wrote in message ... Thanks much Julie...that did work...can you help me with this one? First here is a sample: D2 = initial date E2 = Due date 48hrs Tue, Jan 18/05,5:00 PM Thu, Jan 20/05,5:00 PM L2 = Due date 10 days from initial date Tue, Feb 01/05,12:00 AM Typically info is sent out after 5pm this gives one day to recieve and review hence 48hrs due date..okay lets say it goes out on Thursday..the due date needs to be Monday..also, on Friday, the due date needs to be Tuesday...How do I modify to this to be correct =IF(NETWORKDAYS(D5,D5+2)<2,D5+3,D5+2) Second...how do I modify the workday function in L2 to also give me the time when it is due? Thanx again "JulieD" wrote: Hi use the WORKDAY function =WORKDAY(A1,10) where A1 is the initial date and 10 is the number of working days to add to the date (this formula also excludes sat & sun) - you can also exclude holidays in the third parameter - check out help for details. Note: you need the analysis tool-pak installed to use this formula Cheers JulieD "Dmorri254" wrote in message ... Hello all, I am trying to create a formlua that will tell me a due date ten business days from the initial date. I currently have a formula that will give me the due date based on 24hrs from the initial date (business days) but I also need one to expend out ten business days can you help me modify this formula to give me a due date ten business days from an initial date...note** this formula excludes weekends... =IF(NETWORKDAYS(D2,D2+1)<2,D2+3,D2+1) Thank you |
#7
|
|||
|
|||
Hi
ignore the last post and try this E2: =WORKDAY(D3,2,H3)+TIMEVALUE(TEXT(D3,"hh:mm")) L2: =WORKDAY(D3,10,H3)+TIMEVALUE(TEXT(D3,"hh:mm")) Hope this gives you what you need. Cheers JulieD "JulieD" wrote in message ... Hi is the time always 5pm if so change the formulas to E2: =WORKDAY(D3,2,H3)+(17/24) L2: =WORKDAY(D3,10,H3)+(17/24) (the H3 refered to in the formula contains the date of the holiday, adjust as necessary) - if it's not always 5pm or might not be that time in the future, please let me know Cheers JulieD "Dmorri254" wrote in message ... This did not work what did I do wrong?? Here is a sample of what was returned using your advice each excludes the jan17th holiday D2 = initial date E2 = should be 48hrs from D2 (1/18/05 5:00PM) Fri, Jan 14/05,5:00 PM returned Wed, Feb 02/05,5:00 PM L2 = should be 10days from D2 (1/31/05 5:00pm) returned Fri, Feb 14/05,5:00 PM "JulieD" wrote: Hi a couple of things, firstly in E2 i would us the formula =WORKDAY(D3,2) which will give you the date 2 days (48hrs) after the initial date, excluding weekends to put the time in the E3 formula use =WORKDAY(D3,2)+MOD(D3,24) and for L3, use the formula =WORKDAY(D3,10)+MOD(D3,24) Cheers JulieD "Dmorri254" wrote in message ... Thanks much Julie...that did work...can you help me with this one? First here is a sample: D2 = initial date E2 = Due date 48hrs Tue, Jan 18/05,5:00 PM Thu, Jan 20/05,5:00 PM L2 = Due date 10 days from initial date Tue, Feb 01/05,12:00 AM Typically info is sent out after 5pm this gives one day to recieve and review hence 48hrs due date..okay lets say it goes out on Thursday..the due date needs to be Monday..also, on Friday, the due date needs to be Tuesday...How do I modify to this to be correct =IF(NETWORKDAYS(D5,D5+2)<2,D5+3,D5+2) Second...how do I modify the workday function in L2 to also give me the time when it is due? Thanx again "JulieD" wrote: Hi use the WORKDAY function =WORKDAY(A1,10) where A1 is the initial date and 10 is the number of working days to add to the date (this formula also excludes sat & sun) - you can also exclude holidays in the third parameter - check out help for details. Note: you need the analysis tool-pak installed to use this formula Cheers JulieD "Dmorri254" wrote in message ... Hello all, I am trying to create a formlua that will tell me a due date ten business days from the initial date. I currently have a formula that will give me the due date based on 24hrs from the initial date (business days) but I also need one to expend out ten business days can you help me modify this formula to give me a due date ten business days from an initial date...note** this formula excludes weekends... =IF(NETWORKDAYS(D2,D2+1)<2,D2+3,D2+1) Thank you |
#8
|
|||
|
|||
Thanx Julie for your patience in this...I was just told the initial time is
always after 4pm CST but not before 4pm CST...how does this affect the formulas? Also as far as the holidays, I an going ot create a range with the dates for the year is this the correct approach?? Thanx again David "JulieD" wrote: Hi is the time always 5pm if so change the formulas to E2: =WORKDAY(D3,2,H3)+(17/24) L2: =WORKDAY(D3,10,H3)+(17/24) (the H3 refered to in the formula contains the date of the holiday, adjust as necessary) - if it's not always 5pm or might not be that time in the future, please let me know Cheers JulieD "Dmorri254" wrote in message ... This did not work what did I do wrong?? Here is a sample of what was returned using your advice each excludes the jan17th holiday D2 = initial date E2 = should be 48hrs from D2 (1/18/05 5:00PM) Fri, Jan 14/05,5:00 PM returned Wed, Feb 02/05,5:00 PM L2 = should be 10days from D2 (1/31/05 5:00pm) returned Fri, Feb 14/05,5:00 PM "JulieD" wrote: Hi a couple of things, firstly in E2 i would us the formula =WORKDAY(D3,2) which will give you the date 2 days (48hrs) after the initial date, excluding weekends to put the time in the E3 formula use =WORKDAY(D3,2)+MOD(D3,24) and for L3, use the formula =WORKDAY(D3,10)+MOD(D3,24) Cheers JulieD "Dmorri254" wrote in message ... Thanks much Julie...that did work...can you help me with this one? First here is a sample: D2 = initial date E2 = Due date 48hrs Tue, Jan 18/05,5:00 PM Thu, Jan 20/05,5:00 PM L2 = Due date 10 days from initial date Tue, Feb 01/05,12:00 AM Typically info is sent out after 5pm this gives one day to recieve and review hence 48hrs due date..okay lets say it goes out on Thursday..the due date needs to be Monday..also, on Friday, the due date needs to be Tuesday...How do I modify to this to be correct =IF(NETWORKDAYS(D5,D5+2)<2,D5+3,D5+2) Second...how do I modify the workday function in L2 to also give me the time when it is due? Thanx again "JulieD" wrote: Hi use the WORKDAY function =WORKDAY(A1,10) where A1 is the initial date and 10 is the number of working days to add to the date (this formula also excludes sat & sun) - you can also exclude holidays in the third parameter - check out help for details. Note: you need the analysis tool-pak installed to use this formula Cheers JulieD "Dmorri254" wrote in message ... Hello all, I am trying to create a formlua that will tell me a due date ten business days from the initial date. I currently have a formula that will give me the due date based on 24hrs from the initial date (business days) but I also need one to expend out ten business days can you help me modify this formula to give me a due date ten business days from an initial date...note** this formula excludes weekends... =IF(NETWORKDAYS(D2,D2+1)<2,D2+3,D2+1) Thank you |
#9
|
|||
|
|||
BINGO!!!!!!!!!! I think that worked....you are AWESOME!!!!.....thanxs again...
David "JulieD" wrote: Hi ignore the last post and try this E2: =WORKDAY(D3,2,H3)+TIMEVALUE(TEXT(D3,"hh:mm")) L2: =WORKDAY(D3,10,H3)+TIMEVALUE(TEXT(D3,"hh:mm")) Hope this gives you what you need. Cheers JulieD "JulieD" wrote in message ... Hi is the time always 5pm if so change the formulas to E2: =WORKDAY(D3,2,H3)+(17/24) L2: =WORKDAY(D3,10,H3)+(17/24) (the H3 refered to in the formula contains the date of the holiday, adjust as necessary) - if it's not always 5pm or might not be that time in the future, please let me know Cheers JulieD "Dmorri254" wrote in message ... This did not work what did I do wrong?? Here is a sample of what was returned using your advice each excludes the jan17th holiday D2 = initial date E2 = should be 48hrs from D2 (1/18/05 5:00PM) Fri, Jan 14/05,5:00 PM returned Wed, Feb 02/05,5:00 PM L2 = should be 10days from D2 (1/31/05 5:00pm) returned Fri, Feb 14/05,5:00 PM "JulieD" wrote: Hi a couple of things, firstly in E2 i would us the formula =WORKDAY(D3,2) which will give you the date 2 days (48hrs) after the initial date, excluding weekends to put the time in the E3 formula use =WORKDAY(D3,2)+MOD(D3,24) and for L3, use the formula =WORKDAY(D3,10)+MOD(D3,24) Cheers JulieD "Dmorri254" wrote in message ... Thanks much Julie...that did work...can you help me with this one? First here is a sample: D2 = initial date E2 = Due date 48hrs Tue, Jan 18/05,5:00 PM Thu, Jan 20/05,5:00 PM L2 = Due date 10 days from initial date Tue, Feb 01/05,12:00 AM Typically info is sent out after 5pm this gives one day to recieve and review hence 48hrs due date..okay lets say it goes out on Thursday..the due date needs to be Monday..also, on Friday, the due date needs to be Tuesday...How do I modify to this to be correct =IF(NETWORKDAYS(D5,D5+2)<2,D5+3,D5+2) Second...how do I modify the workday function in L2 to also give me the time when it is due? Thanx again "JulieD" wrote: Hi use the WORKDAY function =WORKDAY(A1,10) where A1 is the initial date and 10 is the number of working days to add to the date (this formula also excludes sat & sun) - you can also exclude holidays in the third parameter - check out help for details. Note: you need the analysis tool-pak installed to use this formula Cheers JulieD "Dmorri254" wrote in message ... Hello all, I am trying to create a formlua that will tell me a due date ten business days from the initial date. I currently have a formula that will give me the due date based on 24hrs from the initial date (business days) but I also need one to expend out ten business days can you help me modify this formula to give me a due date ten business days from an initial date...note** this formula excludes weekends... =IF(NETWORKDAYS(D2,D2+1)<2,D2+3,D2+1) Thank you |
#10
|
|||
|
|||
Hi David
glad we nailed it! thanks for the feedback. Cheers JulieD "Dmorri254" wrote in message ... BINGO!!!!!!!!!! I think that worked....you are AWESOME!!!!.....thanxs again... David "JulieD" wrote: Hi ignore the last post and try this E2: =WORKDAY(D3,2,H3)+TIMEVALUE(TEXT(D3,"hh:mm")) L2: =WORKDAY(D3,10,H3)+TIMEVALUE(TEXT(D3,"hh:mm")) Hope this gives you what you need. Cheers JulieD "JulieD" wrote in message ... Hi is the time always 5pm if so change the formulas to E2: =WORKDAY(D3,2,H3)+(17/24) L2: =WORKDAY(D3,10,H3)+(17/24) (the H3 refered to in the formula contains the date of the holiday, adjust as necessary) - if it's not always 5pm or might not be that time in the future, please let me know Cheers JulieD "Dmorri254" wrote in message ... This did not work what did I do wrong?? Here is a sample of what was returned using your advice each excludes the jan17th holiday D2 = initial date E2 = should be 48hrs from D2 (1/18/05 5:00PM) Fri, Jan 14/05,5:00 PM returned Wed, Feb 02/05,5:00 PM L2 = should be 10days from D2 (1/31/05 5:00pm) returned Fri, Feb 14/05,5:00 PM "JulieD" wrote: Hi a couple of things, firstly in E2 i would us the formula =WORKDAY(D3,2) which will give you the date 2 days (48hrs) after the initial date, excluding weekends to put the time in the E3 formula use =WORKDAY(D3,2)+MOD(D3,24) and for L3, use the formula =WORKDAY(D3,10)+MOD(D3,24) Cheers JulieD "Dmorri254" wrote in message ... Thanks much Julie...that did work...can you help me with this one? First here is a sample: D2 = initial date E2 = Due date 48hrs Tue, Jan 18/05,5:00 PM Thu, Jan 20/05,5:00 PM L2 = Due date 10 days from initial date Tue, Feb 01/05,12:00 AM Typically info is sent out after 5pm this gives one day to recieve and review hence 48hrs due date..okay lets say it goes out on Thursday..the due date needs to be Monday..also, on Friday, the due date needs to be Tuesday...How do I modify to this to be correct =IF(NETWORKDAYS(D5,D5+2)<2,D5+3,D5+2) Second...how do I modify the workday function in L2 to also give me the time when it is due? Thanx again "JulieD" wrote: Hi use the WORKDAY function =WORKDAY(A1,10) where A1 is the initial date and 10 is the number of working days to add to the date (this formula also excludes sat & sun) - you can also exclude holidays in the third parameter - check out help for details. Note: you need the analysis tool-pak installed to use this formula Cheers JulieD "Dmorri254" wrote in message ... Hello all, I am trying to create a formlua that will tell me a due date ten business days from the initial date. I currently have a formula that will give me the due date based on 24hrs from the initial date (business days) but I also need one to expend out ten business days can you help me modify this formula to give me a due date ten business days from an initial date...note** this formula excludes weekends... =IF(NETWORKDAYS(D2,D2+1)<2,D2+3,D2+1) Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NETWORKDAYS | New Users to Excel | |||
UUsing the NetworkDays Function with IF Statements | Excel Worksheet Functions | |||
networkdays or dias.lab | Links and Linking in Excel | |||
Networkdays shows as #NAME even though I have the toolpack instal. | Excel Discussion (Misc queries) | |||
MS Excel Function - Networkdays | Excel Worksheet Functions |