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)
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.
sd = GetAsDate( startDate ) ;
weekdayBits = "0111110" ;
subBits = RotateLeft( weekdayBits ; DayOfWeek(sd) - 1 ) ;
sd + 7 * Div( weekDays ; 5 ) +
Position( subBits ; "1" ; 1 ; 1+ Mod( weekDays ; 5 ) ) - 1
// RotateLeft( theString ; howMany )
len = Length( theString ) ;
howMany = Mod( howMany ; len ) ;
result = Right( theString ; len - howMany ) & Left( theString ; howMany )
I've had good luck with this custom function from Brian Dunning's site:
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.
lizdell wrote: I would like the +5 or the +20 to count only work days.
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.
Work Days are Monday thru Friday, I don't need to worry about holidays
lizdell wrote: Work Days are Monday thru Friday, I don't need to worry about holidays
Then I'd suggest you look at:
Thanks for your help everyone. I got this to work now.
WOW. That calc is short and sweet and no custom functions required. Very nice!
Retrieving data ...