7 Replies Latest reply on Feb 28, 2012 5:32 PM by LaRetta

# How do I add days of the week only to my IF statement?

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)

• ###### 1. Re: How do I add days of the week only to my IF statement?

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

• ###### 2. Re: How do I add days of the week only to my IF statement?

lizdell wrote:

I would like the +5 or the +20 to count only work days.

Please state what "work days" are, and how (if at all) do you want to handle holidays.

• ###### 3. Re: How do I add days of the week only to my IF statement?

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

• ###### 4. Re: How do I add days of the week only to my IF statement?

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

)

======

• ###### 5. Re: How do I add days of the week only to my IF statement?

lizdell wrote:

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

Then I'd suggest you look at: