Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Programing | New Users to Excel | |||
Proper Programing | Excel Programming | |||
VB programing | Excel Programming | |||
Help with VBA programing | Excel Programming | |||
help with excel programing | Excel Programming |