formula for programing end dates
 

I'm looking for the formula to do the following: to project a date in the
future. this date can "only" be on a Friday, Saturday or Sunday. sample:
I'm off work Friday thru Sunday, I take a part time job that will last 72
days. what would the completion date be? I have been unable to figure the
formula. can anyone help?

formula for programing end dates
 

Function EndDate(StartDate As Date, Optional hours As Long = 72) As Date
EndDate = DateAdd("h", hours, StartDate)
Do While Weekday(EndDate, vbMonday) < 5
EndDate = EndDate + 1
Loop
End Function

"warren" wrote:

I'm looking for the formula to do the following: to project a date in the
future. this date can "only" be on a Friday, Saturday or Sunday. sample:
I'm off work Friday thru Sunday, I take a part time job that will last 72
days. what would the completion date be? I have been unable to figure the
formula. can anyone help?

formula for programing end dates
 

Here is a formula solution which caters for holidays as well

=start_date+SIGN(B1)*SMALL(IF((WEEKDAY(start_date+ SIGN(days)*(ROW(INDIRECT("
1:"&ABS(days)*10))))={2,3,4,5})*ISNA(MATCH(start_d ate+SIGN(days)*(ROW(INDIRE
CT("1:"&ABS(days)*10))),holidays,0)),ROW(INDIRECT( "1:"&ABS(days)*10))),ABS(d
ays))

This is an array formula so commit with Ctrl-Shift-Enter.

Holidays is a range of holiday dates, it can be empty but it must be
defined.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"warren" wrote in message
...
I'm looking for the formula to do the following: to project a date in the
future. this date can "only" be on a Friday, Saturday or Sunday. sample:
I'm off work Friday thru Sunday, I take a part time job that will last 72
days. what would the completion date be? I have been unable to figure the
formula. can anyone help?

formula for programing end dates
 

I tried both Patrick and Bob's formula but I could not get them to work.
I'm still looking for help.....please

"warren" wrote:

I'm looking for the formula to do the following: to project a date in the
future. this date can "only" be on a Friday, Saturday or Sunday. sample:
I'm off work Friday thru Sunday, I take a part time job that will last 72
days. what would the completion date be? I have been unable to figure the
formula. can anyone help?

formula for programing end dates
 

Warren,

mine might have suffered from NG wrap-around, so check it carefully.

What data did you use, and what result did you get?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"warren" wrote in message
...
I tried both Patrick and Bob's formula but I could not get them to work.
I'm still looking for help.....please

"warren" wrote:

I'm looking for the formula to do the following: to project a date in
the
future. this date can "only" be on a Friday, Saturday or Sunday.
sample:
I'm off work Friday thru Sunday, I take a part time job that will last
72
days. what would the completion date be? I have been unable to figure
the
formula. can anyone help?

formula for programing end dates
 

.... oh, and did you replace start_date, days and holidays or create range
names for these?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"warren" wrote in message
...
I tried both Patrick and Bob's formula but I could not get them to work.
I'm still looking for help.....please

"warren" wrote:

I'm looking for the formula to do the following: to project a date in
the
future. this date can "only" be on a Friday, Saturday or Sunday.
sample:
I'm off work Friday thru Sunday, I take a part time job that will last
72
days. what would the completion date be? I have been unable to figure
the
formula. can anyone help?

formula for programing end dates
 

I loaded just as you wrote it double checked it. in the box where the answer
should have been was "#name"

=start_date+SIGN(B1)*SMALL(IF((WEEKDAY(start_date+ SIGN(days)*(ROW(INDIRECT("
1:"&ABS(days)*10))))={2,3,4,5})*ISNA(MATCH(start_d ate+SIGN(days)*(ROW(INDIRE
CT("1:"&ABS(days)*10))),holidays,0)),ROW(INDIRECT( "1:"&ABS(days)*10))),ABS(d
ays))


Bob I give you another sample of what I'm looking for:

I'm going to jail and only serve on weekends (friday thru Sunday) I have to
serve (?) days when do I get out?

thanks for any help...Warren


"Bob Phillips" wrote:

Warren,

mine might have suffered from NG wrap-around, so check it carefully.

What data did you use, and what result did you get?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"warren" wrote in message
...
I tried both Patrick and Bob's formula but I could not get them to work.
I'm still looking for help.....please

"warren" wrote:

I'm looking for the formula to do the following: to project a date in
the
future. this date can "only" be on a Friday, Saturday or Sunday.
sample:
I'm off work Friday thru Sunday, I take a part time job that will last
72
days. what would the completion date be? I have been unable to figure
the
formula. can anyone help?