Date calculation excluding weekend

system · July 16, 2010, 1:03am

I am using Business Object desktop intelligence and trying to come up with a formula to count days in between two date fields. I have two date fields say start date and end date.I need to find out the days between these two dates excluding Weekends(saturday and sunday’s). The current BO universe doens’t have a calendar table and I don’t have the rights to create a table. How can I accomplish at the report level?? Any help would be appreciated.

Thanks.

boboleft (BOB member since 2010-07-16)

system · July 16, 2010, 5:00am

Hi and Welcome to B B

Have a look at this post from David Rathbun. He presented this at the Mastering Business Objects conference this year in Melbourne.

http://www.dagira.com/2009/10/23/calculating-business-days-between-two-days-via-report-functions/#more-160

plessiusa (BOB member since 2004-03-22)

system · July 16, 2010, 2:14pm

Hi,

You can try this post too.

https://bobj-board.org/t/157452

Rajubollas (BOB member since 2009-09-04)

system · July 17, 2010, 11:11pm

Thanks for the information.

boboleft (BOB member since 2010-07-16)

system · July 19, 2010, 1:19pm

Hi

I am trying to give you the solution in SQL level, please try this it’s working for me and I used this formul’e as dummy object in report level.

in the below eg: getdate() is your system date if you want you can replace with end date.

=5*DATEDIFF(ww,(START DATE),getdate())+DATEPART(dw,getdate())-DATEPART(dw,(START DATE)) .
Thanks!

Prince. (BOB member since 2009-11-17)

system · July 20, 2010, 9:51pm

Hi,

I don’t have access to do query at the SQL level. thanks for the help.

I have tried the formula by right clicking on the “fomulas” folder and adding new variable and kept getting error message " Not enough parameters (DMB0006) ". Can’t figure out where I did wrong…Does anybody know what this message means?? Thanks in advance for the help.

=Floor(DaysBetween([Date1];[Date2])/7)*5+If (DayNumberOfWeek([Date2])>=DayNumberOfWeek([Date1]);
DayNumberOfWeek([Date2])-DayNumberOfWeek([Date1]);
DayNumberOfWeek ([Date2])-DayNumberOfWeek([Date1])+5)

boboleft (BOB member since 2010-07-16)

system · July 21, 2010, 2:40am

Are you using Webi or Deski? Because you have posted the same question in three different topics…

Dave Rathbun (BOB member since 2002-06-06)

system · July 21, 2010, 8:25am

Hi

I given the solution in SQL but no need to have the access or write the same in Free hand SQL or in your database to solve your problem

Please follow this steps:

Open your report data porvider and drag one dummy object(other that required report field) into your query panel.
Open Query Panel SQL and now you will see the dummy object, replace the dummy object with the below code.

5*DATEDIFF(ww,(START DATE),getdate())+DATEPART(dw,getdate())-DATEPART(dw,(START DATE))

Note: getdate() is your system date you can replace with end date column.
& START DATE is also you can replace with the start date column.

Check Do Not Generate SQL before running and check the SQL syntax is write or not.
Run the query, now in the left hand side in variable section you will see the dummy object, drag the same into report level.

It will work, Trust me…

Prince. (BOB member since 2009-11-17)

system · July 21, 2010, 10:50am

Hi Prince, quick question for you. Which database is the OP using?

pablolee (BOB member since 2008-07-29)

system · July 21, 2010, 11:20am

Hi

I am sorry, i forgot to mention the database am using it’s

SQL SERVER 2005… and this logic will work only if database is sql server.

& I saw so many answers for the same question in this blog…

Different people, diffrent thinking, diffrent views…what an idea …hey na.

Prince. (BOB member since 2009-11-17)

system · July 21, 2010, 10:00pm

Prince,

Thanks for your suggestion, yes it will work, but it is not recommended to do changes to the SQL generated by the universe for the main reason of future maintenance.
If you add this logic to a report and sometime in the future, you or someone else decide to add a new object to the report and don’t realise that the SQL has been manually altered, Business Objects regenerates the SQL automatically and that original logic will break.
Should this go unnoticed, it is possible that this change could end up in a production environment and cause undesireable results with possible major impact.

So what the original poster can do is test this logic in the report using the Free Hand SQL, then if it works, put in a request for a new object to be added to the universe so that it can be consumed in the report directly.

However should there be no other way around this but to add the Free Hand SQL, then it should be documented in the report somewhere so that anybody doing future maintenance will see what the note and identify the change before adding any new objects.

plessiusa (BOB member since 2004-03-22)

system · July 22, 2010, 4:04am

Thanks for everyone’s input. Are there any other ways to do this besides using SQL??

boboleft (BOB member since 2010-07-16)

system · July 22, 2010, 4:40am

Did you read the post attached to my first reply, this one does the whole thing in the report. No SQL changes required.

plessiusa (BOB member since 2004-03-22)

system · July 22, 2010, 7:33am

Hi Arjan

Accepted & that’s why i mentioned so many answers regarding the same quetion, just i given this solution because i used once.

I am really impressed with DAVE’s solution…

Any way Thanks & will keep your suggessions in my mind.

Thanks!

Prince. (BOB member since 2009-11-17)

system · November 9, 2010, 10:54pm

I’m sure there are other solutions. However, I created the following formula which suited my needs for calculating the number of days within a date range minus Saturdays and Sundays:

=DaysBetween( , )+1-Length(Replace(SubStr(Fill(“0000011” ,(DaysBetween( , )+1)/7+2) ,DayNumberOfWeek() ,DaysBetween( , )+1) ,“0” ,“”))

The BOLD section could be removed to just calculate the total Saturdays and Sundays in a given date range.

Hope this helps…

David P. (BOB member since 2010-11-09)

system · November 11, 2010, 5:29pm

David

Thanks for your suggestion; a couple question however. It appears that this formula is counting the first date.

I want to count the days between pickup and delivery for on time performance and the formula returns “2” for days between 11/1 and 11/2; I need it to return “1” and not count the pickup date.

Also, if you do not mind can you explain what the formula is doing? I have a block and it is not registering…

thanks again

REB01 (BOB member since 2004-11-29)

system · November 12, 2010, 3:00am

Remove the two occurances of “+1” from the formula and that’ll give you the non-inclusive count you are seeking.

I racked my brains for hours trying to get a straight forward “numeric” based formula but the various solutions always have some condition that wouldn’t work. So… I took a much simpler approach explained as follows (I’m new to BOE/BOB):

=DaysBetween( , )+1-Length(Replace(SubStr(Fill(“0000011” ,(DaysBetween( , )+1)/7+2) ,DayNumberOfWeek() ,DaysBetween( , )+1) ,“0” ,""))

The above formula does the following:

The “0000011” represents the days of the weeks (0=M-F, 1=Sat/Sun)

(1) DaysBetween( , )+1)/7+2) determines number of weeks plus 2 extra for padding (inclusive)
(2) Fill(“0000011” ,(DaysBetween( , )+1)/7+2) generates a string of “00000110000011000001100000110000011” depending of the number of weeks plus 2 extra weeks
(3) SubStr(Fill(“0000011” ,(DaysBetween( , )+1)/7+2) ,DayNumberOfWeek() ,DaysBetween( , )+1) uses the DayNumberOfWeek function to determine which day-of-the-week is the first day then extracts the total number of days. For example: if the first day was a Wednesday, it would start on the 3rd zero from the left and extract the total number of days. New value may look like “000110000011000001100”.
(4) Replace(SubStr(Fill(“0000011” ,(DaysBetween( , )+1)/7+2) ,DayNumberOfWeek() ,DaysBetween( , )+1) ,“0” ,"") removes the the zeroes (replaces with nulls) leaving only the ones which represent every Saturday/Sunday.
(5) Length(Replace(SubStr(Fill(“0000011” ,(DaysBetween( , )+1)/7+2) ,DayNumberOfWeek() ,DaysBetween( , )+1) ,“0” ,"")) counts how many ones are left which equals total number of Saturdays/Sundays.
(6) By adding the DaysBetween( , )+1 to the front of the formula it calculates total weekdays (Total minus all Saturdays/Sundays)

Hope this makes sense…

David P. (BOB member since 2010-11-09)

system · November 15, 2010, 4:40pm

hi all,
i had viewed Dave’s presentation on calculating business days between two days, and he is a real genius i must say, and i admire him a lot.
i too tried based on trial and error method and probably its working , tested on all combination of start and end dates whether its starting on weekend and ending on weekday whatever. the number of days between will be inclusive of these two days depending on whether they are business days or not.

attached is the report excel copy which i have built.

1.)contains the free hand sql query data provider.
(assuming the owner who started this post is free to use free hand sql.you mentioned you dont have any calendar date object and no universe modification rights)

 
select 
to_char(@prompt('Date1','D',,mono,free,))as startdate, 
to_char(@prompt('Date2','D',,mono,free,))as enddate 
from dual

2.)and the variable formula which gives the number of business days.
the variable is quite huge as its taking into consideration all the possibilities.


= If  (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) =6) 
     And (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) =6) 
 Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-1) 
 Else If (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) =6) 
     And (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) =7) 
 Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-2)
 Else If (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM"))=7)
     And(DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) =7) 
 Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-1)
 Else If (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) =7) 
 And(DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) =6) 
 Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-2)
Else If (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) =6) 
    And(DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) InList(1 ,2 ,3 ,4 ,5)) 
Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-2)
Else If (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) =7) 
    And(DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) InList(1 ,2 ,3 ,4 ,5)) 
Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-1)
Else If (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) InList(1 ,2 ,3 ,4 ,5)) 
   And(DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) =7)
Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-2)
Else If (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) InList(1 ,2 ,3 ,4 ,5)) 
  And(DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) =6) 
Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-1)
Else (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2))

very Thanks,
if some one just reviews this report out for me.
Dhiren
aHah!!!
business days between 2 days.xls (18.0 KB)

dhirensk (BOB member since 2010-04-04)

system · November 15, 2010, 9:34pm

Dhiren…

Unless I’m missing something, I found some date ranges that include a weedend in the count. I randomly found the following:
2/15/08 & 2/19/08 --> Yields 5 but s/b 3
8/7/09 & 8/18/09 --> Yields 10 but s/b 8

If I followed the logic correctly, the above date ranges end up following the very last “ELSE” statement which seems to be including a weekend (end-to-end delta).

Let me know if my analyse/check is incorrect. I’d like to know were I went wrong…

David P. (BOB member since 2010-11-09)

system · November 16, 2010, 11:47am

hey David ,
really thanks for reviewing it. you really pointed out the probabilities which i missed in this formula.

when both dates are work dates and present in the same week
when both dates are work dates and belong to different week.

the last else part in the earlier formula was not considering these conditions. Just updated the formula.
really appreciate for pointing this out to me. very thanks!!!

 = If  (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) =6) 
     And (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) =6) 
 Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-1) 
 Else If (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) =6) 
     And (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) =7) 
 Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-2)
 Else If (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM"))=7)
     And(DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) =7) 
 Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-1)
 Else If (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) =7) 
 And(DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) =6) 
 Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-2)
Else If (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) =6) 
    And(DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) InList(1 ,2 ,3 ,4 ,5)) 
Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-2)
Else If (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) =7) 
    And(DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) InList(1 ,2 ,3 ,4 ,5)) 
Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-1)
Else If (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) InList(1 ,2 ,3 ,4 ,5)) 
   And(DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) =7)
Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-2)
Else If (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) InList(1 ,2 ,3 ,4 ,5)) 
  And(DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) =6) 
Then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-(Floor((DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")))/7)*2)-1)
Else if (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM"))<7)
and 
(Week(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM"))<>Week(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) ) 
then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")+1)-2)
else if (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM"))>7)
and (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) InList(1 ,2 ,3 ,4 ,5)) 
and (DayNumberOfWeek(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM")) InList(1 ,2 ,3 ,4 ,5)) 
then (DaysBetween (ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM") ,ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM"))+1)
-((Week(ToDate(UserResponse("dpdate" ,"Date2") ,"mm/dd/yyyy h:mm:ss AM/PM"))-Week(ToDate(UserResponse("dpdate" ,"Date1") ,"mm/dd/yyyy h:mm:ss AM/PM")) )*2)

dhirensk (BOB member since 2010-04-04)