Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2007
    Posts
    54

    Unanswered: Algorithm to count weekdays between two dates

    I am using Crystal Reports 9.

    Can anyone help me with a simple algorithm to calculate the number of weekdays (Monday - Friday) between two dates, please?

    I have various ideas but do not want to reinvent the wheel if someone knows a dead simple way.

    My theory so far is to use 5 days for each whole week passed so far and then use the DayOfWeek function to find the days of the StartDate and EndDate and use them accordingly:

    5 * INT( ({@EndDate}-{@StartDate})/7 ) +
    if ( DayOfWeek({@EndDate})-DayOfWeek({@StartDate}) ) < 0
    then 5 - ( DayOfWeek({@EndDate})-DayOfWeek({@StartDate}) )
    else ( DayOfWeek({@EndDate})-DayOfWeek({@StartDate}) )

    NB I am ignoring holidays and assuming that the users have selected a weekday for both {@StartDate} and {@EndDate}, however this assumption may not be a fair one!!!

    Many thanks,
    Caspar

  2. #2
    Join Date
    Feb 2007
    Posts
    348
    I don't have my copy of Crystal in front of me but I'm fairly sure this function already exists. I will confirm in a couple hours when I have access.

  3. #3
    Join Date
    Jul 2007
    Posts
    54
    Many thanks for swift reply. I'll have a look too. However please note that I have version 9 and so it must be a solution available withing that version.

    Many thanks,
    Caspar

  4. #4
    Join Date
    Feb 2007
    Posts
    348
    I grabbed this out of the crystal help document for DateDiff

    For example, suppose you want to calculate the number of days between the order date and ship date, excluding Saturdays and Sundays:
    Rem Basic syntax
    Dim d1, d2
    d1 = {Orders.Order Date}
    d2 = {Orders.Ship Date}
    formula = DateDiff("d", d1, d2) - _
    DateDiff("ww", d1, d2, crSaturday) - _
    DateDiff("ww", d1, d2, crSunday)

    //Crystal syntax
    Local DateTimeVar d1 := {Orders.Order Date};
    Local DateTimeVar d2 := {Orders.Ship Date};
    DateDiff ("d", d1, d2) -
    DateDiff ("ww", d1, d2, crSaturday) -
    DateDiff ("ww", d1, d2, crSunday)


    I haven't tested it and I don't have nine. Give it a go and let me know. If you don't have the functions, I'll see what else I can find.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •