Omit weekends in date calculation in report

lodger

I run a report that calculates the difference in days between the sales invoice header shipment date and the sales invoice header posting date. I need the report to ignore weekends and calculate the difference between a Friday and the following Tuesday as 2 days and not 4 days.

Is there any method I can use?

Thanks in advance.

matttrax

Not sure if my logic is quite right on this, but I think you could do something similar.

You get an integer representing the number of days when you subtract a date from another date. So if you did 12312007D - 01022007D you would get 363. If you divide this number by 7 then you get the number of weeks that have elapsed. 363 / 7 = 51.857, or 51+ weeks. 51 * 5 weekdays is 252 days.

There would need to be some boundary checking because in your case a Friday to Monday would only be 2 days, but would come out to less than 1 week and thus 0 days in the sample above. Hopefully it's a start for you.

Savatage

maybe this can work for you?

http://www.mibuso.com/forum/viewtopic.php?t=2565

matttrax

Yeah, you could do this I guess:

tempDate := startDate;
WHILE tempDate <> endDate DO BEGIN
  IF (DATE2DWY(tempDate, 1) >= 1) AND
      (DATE2DWY(tempDate, 1) <= 5) THEN
         totalWeekDays := totalWeekdays + 1
  tempDate := CALCDATE(tempDate, '+1D');
END;

fl

And what about other holidays.

If you need to take them into account its best to use the base calendar to count this days. You can step day by day through this calendar from shipping day to invoicing day, and count only those days that are working days.

David_Cox

Hi I found this Idea in a post by Sander7 =D>

I remembered looking at this post and thought it was a good solution for you, I have tested it and it worked fine. \:D/


Calendar.RESET;
Calendar.SETRANGE("Period Type",Calendar."Period Type"::Date);
Calendar.SETRANGE("Period Start","Posting Date",TODAY);
Calendar.SETRANGE("Period No.",1,5);
MESSAGE('%1',Calendar.COUNT);

And I learned something today as well, one I will remember!

David

lodger

Thanks for the responses.

I'll try and use the suggestions in my report and let you know which one works for me.

I'm not a developer so it may take a while.

Thanks
lodger