#1   Report Post  
Dmorri254
 
Posts: n/a
Default 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   Report Post  
JulieD
 
Posts: n/a
Default

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   Report Post  
Dmorri254
 
Posts: n/a
Default

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   Report Post  
JulieD
 
Posts: n/a
Default

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   Report Post  
Dmorri254
 
Posts: n/a
Default

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   Report Post  
JulieD
 
Posts: n/a
Default

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   Report Post  
JulieD
 
Posts: n/a
Default

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   Report Post  
Dmorri254
 
Posts: n/a
Default

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   Report Post  
Dmorri254
 
Posts: n/a
Default

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   Report Post  
JulieD
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
NETWORKDAYS Loi New Users to Excel 1 January 17th 05 08:52 PM
UUsing the NetworkDays Function with IF Statements carl Excel Worksheet Functions 1 December 30th 04 10:12 PM
networkdays or dias.lab Rodrigo Links and Linking in Excel 1 December 17th 04 06:33 PM
Networkdays shows as #NAME even though I have the toolpack instal. mark Excel Discussion (Misc queries) 1 December 1st 04 10:13 PM
MS Excel Function - Networkdays Nilesh Inamdar Excel Worksheet Functions 3 November 26th 04 08:10 PM


All times are GMT +1. The time now is 01:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"