Calculating Working days(Business Days) between two dates

[Nandini.t]

Hi Experts,

I need to calculate number of days between two dates which only shows working days(except saturday & sunday).

I tried to use the below expression
floor(_days_between(date1,date2)/7) * 5 + (_day_of_week(date1) - _day_of_week(date2) )
but no luck.

Any help on this would begreat help as i dint get proper solution for this till now.

Thanks in advance.

[BigChris]

Are date1 and date2 always going to be in the same year? Or do you need to code to accommodate the end/start of the year?

[BigChris]

Actually, I think it might be simpler than I thought. Assuming date2 is later than date 1, then  I think you've just them the wrong way round in your calculation.

floor(_days_between(date2, date1) / 7) * 5 + (_dayof_week(date2) - _day_of_week(date1))

C

[Nandini.t]

Thanks BigChris,

Yes, date1 & date2 are in the same year but there is a possibility like date2 > date1 or either way date1 > date2.

for example,

date1 =End Date,
date2 = start date
given,  start date may be greater than End Date.

[BigChris]

Ok, have you broken down the calculation to see if each element gives you the expected results?

For example, I'd build the calculation up as:

_days_between(date1,date2)

then

_days_between(date1,date2)/7

then

floor(_days_between(date1,date2)/7)

etc. checking that the result at each stage was what you were looking for.

[MDXpressor]

You might also try fixing it before it is used in your _daysBetween() function.

[day1New]:  if ([day1]>[day2]) then ([day2]) else ([day1])
[day2New]:  if ([day1]>[day2]) then ([day1]) else ([day2])

Now you can be sure that [day2New]>[day1New], so you could:
_days_between([day1New],[day2New])

There is no need for the following example, I just wanted to 'days between' until the cows came home...
[day1]=until the cows come home
[day2]=today

[day1New]=today
[day2New]=until the cows come

_days_between([day1New], [day2New])=_days_between(now, until the cows come home)

[navissar]

You're in the right direction, but you need a little push to see it through.
First, you have to cater for dumb users who will select a weekend date as start/end date (You could use JS to verify they don't in the prompt page and pop up an error saying "Don't be a twonk!" or something to that effect if they do, but that's an overkill and users don't always fancy being called a twonk so maybe it's not the best idea..  ;)).
So, as step 1, create a data item called [real from date] which does the following:

Code Select Expand

case when _day_of_week(?startDatePRM?,1)=6 then _add_days(?startDatePRM?,2)
when _day_of_week(?startDatePRM?,1)=7 then _add_days(?startDatePRM?,1)
else ?startDatePRM?
end
This will return the next Monday in case a Saturday or a Sunday have been selected as start date.
As step 2, we need to do the same for end date, so create another data item called [real end date] which does the following:

Code Select Expand

case when _day_of_week(?endDatePRM?,1)=6 then _add_days(?endDatePRM?,-1)
when _day_of_week(?endDatePRM?,1)=7 then _add_days(?endDatePRM?,-2)
else ?endDatePRM?
end
This will return the previous Friday, as this is the relevant last working day for the date selected.

Now, the problem with yours and Chris's calculation is that in one of the cases (Either day of week of start date is bigger than day of week of end date or vice versa) your week count will be reduced AND you'll subtract the day difference. To deal with this, we will always count the difference from the Monday prior to the start date. So, step 3 is:
Create a data item called Monday prior to start date which is:

Code Select Expand

_add_days([real from date],-1*(_day_of_week([real from date],1)-1))
This will return the Monday on the from date week. Note: The gap between from date and the Monday before it is always the day of week of from date minus 1. We'll use that later.
Now, let's calculate the weeks between like this:

Code Select Expand

floor(_days_between([real to date],[Monday prior to start date])/7)
we'll multiply it by 5 to get the working days:

Code Select Expand

floor(_days_between([real to date],[Monday prior to start date])/7)*5
Now we'll add the days from the beginning of the last week to the end date, which are calculated like this:

Code Select Expand

_day_of_week ([real to date],1)-1(if you want to include the end date do not subtract 1)
So the current calculation is:

Code Select Expand

floor(_days_between([real to date],[Monday prior to start date])/7)*5
+(_day_of_week ([real to date],1)-1)
Now we remove the days between the real start date and the Monday before it:

Code Select Expand

floor(_days_between([real to date],[Monday prior to start date])/7)*5
+(_day_of_week ([real to date],1)-1)
-(_day_of_week([real from date],1)-1)
(If you don't want to include the start day subtract 2 instead of 1)

That should do the trick and cater for all cases.