Below is the If statement I use, but I would like the +5 or the +20 to count only work days. Is there any way to do this??

If(Type of Appl="Standard";Date of Appl+5; Date of Appl+20)

Below is the If statement I use, but I would like the +5 or the +20 to count only work days. Is there any way to do this??

If(Type of Appl="Standard";Date of Appl+5; Date of Appl+20)

I've had good luck with this custom function from Brian Dunning's site:

http://www.briandunning.com/cf/721

Basically, you feed it a date and a number of days and it gives you a date. It accounts for working days and even allows you to set a flag to include Saturday as a working day if you like.

HTH

Mike

Since 5 weekdays or 20 weekdays is equal to 1 and 4 weeks, respectively, a simplistic approach would be to add 7 or 28 calendar days, respectively. For example, a Tuesday plus 5 or 20 weekdays will be a Tuesday 1 or 4 weeks later. Now the only problem with this approach is when "Date of Appl" is not a weekday, but a weekend day. In those cases, one would need to add either 2 (for Saturday) or 1 (for Sunday) to the resulting calculation.

Thus, for the +5 weekdays, we have

Date of Appl + 7 + Let( dow = DayOfWeek(Date of Appl) ; Case( dow = 7 ; 2 ; dow = 1 ; 1 ; 0 ) )

and for the +20 weekdays, we have

Date of Appl + 20 + Let( dow = DayOfWeek(Date of Appl) ; Case( dow = 7 ; 2 ; dow = 1 ; 1 ; 0 ) )

Personally, I like to encapsulate long calculations such as these into appropriately-named Custom Functions. But when I build custom functions, I like to make them more general to handle similar tasks, such as "handle different numbers of weekdays other than 5 or 20" or "make them easy to modify to handle different 'weekday' definitions, such as skip Sat, Sun, and Wed, for example".

Here is a custom function I use to handle such (general) calculations; it uses a second custom function, included below it.

======

// AddWeekDays ( startDate ; weekDays )

//

// Each bit in weekdayBits marks a DOW (SMTWTFS) as being counted (1) or not counted (0).

// The weekdayBits array "0111110" indicates Mon-Fri as weekdays and Sun & Sat as off days.

//

Let(

[

sd = GetAsDate( startDate ) ;

weekdayBits = "0111110" ;

subBits = RotateLeft( weekdayBits ; DayOfWeek(sd) - 1 ) ;

result =

sd + 7 * Div( weekDays ; 5 ) +

Position( subBits ; "1" ; 1 ; 1+ Mod( weekDays ; 5 ) ) - 1

] ;

result

)

======

// RotateLeft( theString ; howMany )

Let(

[

len = Length( theString ) ;

howMany = Mod( howMany ; len ) ;

result = Right( theString ; len - howMany ) & Left( theString ; howMany )

] ;

result

)

======

lizdell wrote:

Work Days are Monday thru Friday, I don't need to worry about holidays

Then I'd suggest you look at:

http://fmforums.com/forum/topic/34344-add-or-subtract-working-days/page__p__159362#entry159362

Since 5 weekdays or 20 weekdays is equal to 1 and 4 weeks, respectively, a simplistic approach would be to add 7 or 28 calendar days, respectively. For example, a Tuesday plus 5 or 20 weekdays will be a Tuesday 1 or 4 weeks later. Now the only problem with this approach is when "Date of Appl" is not a weekday, but a weekend day. In those cases, one would need to add either 2 (for Saturday) or 1 (for Sunday) to the resulting calculation.

Thus, for the +5 weekdays, we have

Date of Appl + 7 + Let( dow = DayOfWeek(Date of Appl) ; Case( dow = 7 ; 2 ; dow = 1 ; 1 ; 0 ) )

and for the +20 weekdays, we have

Date of Appl + 20 + Let( dow = DayOfWeek(Date of Appl) ; Case( dow = 7 ; 2 ; dow = 1 ; 1 ; 0 ) )

Personally, I like to encapsulate long calculations such as these into appropriately-named Custom Functions. But when I build custom functions, I like to make them more general to handle similar tasks, such as "handle different numbers of weekdays other than 5 or 20" or "make them easy to modify to handle different 'weekday' definitions, such as skip Sat, Sun, and Wed, for example".

Here is a custom function I use to handle such (general) calculations; it uses a second custom function, included below it.

======

// AddWeekDays ( startDate ; weekDays )

//

// Each bit in weekdayBits marks a DOW (SMTWTFS) as being counted (1) or not counted (0).

// The weekdayBits array "0111110" indicates Mon-Fri as weekdays and Sun & Sat as off days.

//

Let(

[

sd = GetAsDate( startDate ) ;

weekdayBits = "0111110" ;

subBits = RotateLeft( weekdayBits ; DayOfWeek(sd) - 1 ) ;

result =

sd + 7 * Div( weekDays ; 5 ) +

Position( subBits ; "1" ; 1 ; 1+ Mod( weekDays ; 5 ) ) - 1

] ;

result

)

======

// RotateLeft( theString ; howMany )

Let(

[

len = Length( theString ) ;

howMany = Mod( howMany ; len ) ;

result = Right( theString ; len - howMany ) & Left( theString ; howMany )

] ;

result

)

======