This date formula needs help ASAP - FORMULA ATTACHED

[Tony H]

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

[comment]

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 ... "

[Tony H]

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.

[comment]

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/

See also:

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

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

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

Edited July 8, 2008 by Guest

[Tony H]

Thanks so much. Very kind. At your suggestion, I went to work on modifying the milestone period element of the formula. I simply created an element of the calculation that subtracted a day from the milestone period, then, when the calculation was done, put the additional day back in the milestone. All through insert calculated value into the milestone field. Many thanks. Tony