5 Replies Latest reply on Sep 26, 2013 8:50 AM by Matty_1

# Date counting calculation issue

### Title

Date counting calculation issue

### Post

Hi there,

At some point I was pointed to a thread that had a calculation that counted WEEKDAYS ONLY and there seems to be an issue with it.  Maybe I didn't copy it over properly or added in something incorrectly or by accident but here it is:

5 * Int ( ( End Date - Start Date ) / 7 ) +Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( Start Date ) - 1 ) + DayOfWeek ( End Date ) ; 1 ) + 1 - Count (Holidays_Time Off::Date)

As far as I know, all I did was add "+1" before the "- Count (" at the end of the formula to make sure I saw 1 day when the start and end day was on the same day.

My issue is when I have my end date on a Sunday.  Say my start day is Friday and end day Sunday it will show 2.  If I change it to Monday it stays on 2.  If I change it to Saturday it goes down to 1.  So it seems to count Monday in whether your on Sunday or Monday.

Let me know if I've done something wrong.  Thanks!

• ###### 2. Re: Date counting calculation issue

Well David, that's the link to the knowledgeBase article from where this basic calculation is found.

I don't think it was ever intended to work with an end date that falls on the week end. When you think about it, if you aren't counting Saturday and Sunday in your count of days, selecting a weekend day as the start or end date doesn't really make sense.

• ###### 3. Re: Date counting calculation issue

Fair enough, I just thought I would bring it up depending on "user interpretation".  Although our business is only open from Monday to Friday, since I'm a manager it's sometimes important to note when I'm not at all available during the weekends there for some people mark the Saturdays and Sundays off as well.

I do intend on changing the way I count the work days by modifying my relationship and eventually I will no longer need this calculation because I also need to remove stat holidays and arbitrary days where we are not open.  T thought I would throw it up on the thread in case someone was able to solve it.  Figured it was for the greater good.

• ###### 4. Re: Date counting calculation issue

We are required to put a State mandated 3 business day hold on certain transactions. We use a modified form of my Calendar Solution to record which days we are open and which we are closed. It's automated so that weekend dates are automatically marked closed and we just have to click on a few additional holiday dates to mark them as closed. Then these type of date calculations then are simply a matter using a relationship that matches only to "open" day records for dates that are greater than the start date to count related records.

• ###### 5. Re: Date counting calculation issue

Yes exactly,  only difference is I will include in the relationship a third condition which is equal or less then the EndDate.  Then I simply count the records.