Hi all,
Within a script, I have an @Businessdays evaluation as follows:
If xHolidays = “” Then
. strFormula$ = {@BusinessDays([} & dateAdjusted.DateOnly & {];[} & dateResponded.DateOnly & {];1:7)}
Else
. strFormula$ = {@BusinessDays([} & dateReceived.DateOnly & {];[} & dateResponded.DateOnly & {];1:7;} & xHolidays & {)}
End If
varDays = Evaluate(strFormula$)
iResponseDays = varDays(0)-1
The formula works fine, but when the start date or the end date is itself a holiday, it does not skip that day.
I tested this in a test form, with startDT, endDT, Weekend and Holidays as date fields and a button with the following code:
nBD := @if(Holidays=“”;@BusinessDays(startDT;endDT;Weekend) ; =“”;@BusinessDays(startDT;endDT;Weekend; Holidays);
FIELD TestBusinessDays := nBD ;
@Command([RefreshWindow])
When startDT = @Date(@Today) and endDT = @Date(@Tomorrow), and I include today’s date in the Holidays field, the TestBusinessFields is still 1, whereas it should be zero. ???
Is this a mistake in my programming? A bug? If so, how do I circumvent it?
If you have an answer, thanks in advance for your feedback!
Subject: Inclusive not exclusive
Hi,
The help file definition of @BusinessDays includes the following key sentence under
Return Value : “The number of days from startDates to endDates, inclusive, less daysToExclude and datesToExclude that fall within the date range.”
Inclusive is the important word. So the days from today to tomorrow is 2, as it includes today and tomorrow. If one of those days is a holiday, it should return 1.
Hope this helps (and that I’ve got it right!)
Phil
Subject: No, I’ve already corrected for the inclusive
Subject: the correction → iResponseDays = varDays(0)-1
I’ve already taken that into account.
In the document I’ve an extra field with the option not to include the startday in the count, and if you opt not to include, it subtracts a day.
Subject: oops
OK, I didn’t spot that. I’ll have another think and get back to you.
Subject: This formula works for me
Hi Effie, I tested this formula for a computed field and it worked perfectly. I do not make any adjustments for inclusive, and with startDT as 05/04/2012 and endDT as 06/04/2012, I get 2. With Holidays as 05/04/2012 or 06/04/2012 I get 1. With Holidays as 05/04/2012, 06/04/2012 I get 0.
nBD := @if(Holidays=“”;@BusinessDays(startDT;endDT;Weekend) ; Holidays !=“”;@BusinessDays(startDT;endDT;Weekend; Holidays);“”);
@If(@IsError(nBD); 9999; nBD)
the only things I can think of are to make sure your holidays field is a multi-value date/time field and that you use the correct multi-value separator.
Hope this helps,
Phil