This date formula needs help ASAP - FORMULA ATTACHED - Calculation Engine (Define Fields)

July 7, 200817 yr

This formula, designed to calculate an end date based on a stated value in the form of a duration of time, does NOT include the beginning or end day/date in the calculation - but needs to. The formula does makes calculations based on weekdays only. Could someone wise take a look and troubleshoot it, please. Here is the formula:

Let ( [

Array="23456034560145601256012360123412345";

Array2="12345012340123601256014560345623456";

Days=Int(formula_check::time_assigned_to_milestone / 5) * 7;

Offset=Middle(Array;(DayOfWeek(formula_check::start_date) - 1) * 5 + Mod(Abs(formula_check::time_assigned_to_milestone); 5) + 1;1) ;

Offset2=Middle(Array2;(DayOfWeek(formula_check::start_date) - 1) * 5 + Mod(formula_check::time_assigned_to_milestone; 5) + 1;1)

];

Case(

formula_check::exclude weekends<>1;

formula_check::start_date + formula_check::time_assigned_to_milestone;

Case(

formula_check::time_assigned_to_milestone < 0;

formula_check::start_date + Days - Offset;

formula_check::start_date + Days + Offset2)

) // End Case

) // End Let

July 8, 200817 yr

What exactly is your question? I see a formula that you say doesn't do what you want, but I don't see that you've told us what do you want it to do: something like "given this and that, I want to compute ... "

July 8, 200817 yr

Author

Thanks for your interest. Sorry for the confusion. Hope this helps:

I enter the amount of time I want the task to take i.e. 5 days in a field called time_assigned to_milestones). I then enter a starting date in start_date. The calculation formula should tell me the end date - but the calculation should include the actual starting date and the end date in the equation. So, if the start date was a Monday, the end date would be a Friday (not Monday like the formula is currently showing. Thanks again.

July 8, 200817 yr

Well, it's going to be rather weird that way, because if the starting and ending dates are included in the count, then StartDate + 1 workday must return StartDate. Which means that StartDate + 0 must return the PREVIOUS workday!

Anyway, have you tried simply subtracting 1 from the number of required workdays, before feeding it to the formula?

Alternatively, you could try:


Let ( [

d = StartDate + Choose ( Mod ( StartDate - 1 ; 7 ) - 5 ; 2 ; 1 ) ;

w = Div ( n - 1 ; 5 ) ;

r = Mod ( n - 1 ; 5 ) ;

s = Mod ( d ; 7 ) ;

a = s + r > 5 ;

e = d + 7 * w + r + 2 * a 

] ;

e + Choose ( Mod ( e - 1 ; 7 ) - 5 ; 2 ; 1 )

)

where StartDate is the starting date, and n is the number of required workdays.

----

NOTE:

The above is based on:

http://fmforums.com/forum/showtopic.php?tid/162774/