Networkdays

[Guest]

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

 

[JulieD]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[JulieD]

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

 

[Guest]

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

 

[Guest]

BINGO!!!!!!!!!! I think that worked....you are AWESOME!!!!.....thanxs again...

David

 

[JulieD]

Hi David

glad we nailed it! thanks for the feedback.

Cheers
JulieD