Novice Crystal User and dates
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3

Thread: Novice Crystal User and dates

  1. #1
    Join Date
    Jan 2014
    Posts
    3

    Unhappy Novice Crystal User and dates

    I am writing a report in Crystal Report version 9 and what I need to do is take one date (let's call this date create date) and calculate the days difference between another date (let's call this date ship date). How do I accomplish this and also exclude weekends and holidays? Can this be done in Crystal Report 9?

  2. #2
    Join Date
    Jul 2005
    Posts
    1,078

    Re: Novice Crystal User and dates

    Calculating the number of calendar days between two dates is fairly simple. However, if you need to calculate the number of work days, it gets a bit more difficult. Not only do you have to account for weekends, in many instances you also have to account for holidays, which change each year.
    The formula below does both. It requires that you enter your list of holiday dates in a separate formula. The list should not include holidays that will occur on weekends since they are already skipped by the formula. However if you do include a weekend holiday the formula will ignore it. The holiday list can include dates several years in advance. There are even ways to have the formula generate a warning when the holiday list is about to expire (ask me about this). To use this formula, simply copy it into your report and put your field names in on the second and third lines in place of the field names I have used. If your fields are DateTime fields you should put your fields within the DATE() function to convert them to dates. If you get an error you can try changing the first line to WhilePrintingRecords.

    If you want to include holidays you put your holiday list in the second formula and then place this formula in the report header. Then suppress the formula or the section.

    NOTE - this formula counts both the starting date AND the ending date, if both are work days. So if you start on Monday and end on Tuesday, this counts as 2 days.

    Code:
    //Main formula
    WhileReadingRecords;
    Local DateVar Start := {StartDate};   // place your Starting Date here
    Local DateVar End := {EndDate};  // place your Ending Date here
    Local NumberVar Weeks; 
    Local NumberVar Days; 
    Local Numbervar Hol;
    DateVar Array Holidays;
    
    Weeks:= (Truncate (End - dayofWeek(End) + 1 
    - (Start - dayofWeek(Start) + 1)) /7 ) * 5;
    Days := DayOfWeek(End) - DayOfWeek(Start) + 1 + 
    (if DayOfWeek(Start) = 1 then -1 else 0)  + 
    (if DayOfWeek(End) = 7 then -1 else 0);   
    
    Local NumberVar i;
    For i := 1 to Count (Holidays)
    do (if DayOfWeek ( Holidays[i] ) in 2 to 6 and 
         Holidays[i] in start to end then Hol:=Hol+1 );
    Weeks + Days - Hol
    Code:
    //Holiday Listing formula to go into the report header of the report.
    BeforeReadingRecords;
    DateVar Array Holidays := [
    Date (2003,12,25), 
    Date (2003,12,31) 
    ];
    0
    Note: Extracted from Ken Hamady site
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  3. #3
    Join Date
    Feb 2015
    Posts
    11

    Re: Novice Crystal User and dates

    Also, make sure the holiday field is displayed somewhere on the header. You can change the font to white so that it does not display numbers. there are additional fields needed to handle negative results.
    You will need a formula for Days, Holidays, Hours, AdjEnd, and AdjStart. The Days lets you obtain results for Days. The Hours lets you obtain results in Hour format. Have fun! The Adj formulas adjust for any negative results caused by a Holiday or after hours work time.

    Code:
    // days
    WhileReadingRecords;
    Local DateVar Start := Date ({@Adj Start}); // place your Starting Date here
    Local DateVar End := Date ({@Adj End}); // place your Ending Date here
    Local NumberVar Weeks;
    Local NumberVar Days;
    Local Numbervar Hol;
    DateVar Array Holidays;
    
    Weeks:= (Truncate (End - dayofWeek(End) + 1
    - (Start - dayofWeek(Start) + 1)) /7 ) * 5;
    Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
    (if DayOfWeek(Start) = 1 then -1 else 0) +
    (if DayOfWeek(End) = 7 then -1 else 0);
    
    Local NumberVar i;
    For i := 1 to Count (Holidays)
    do (if DayOfWeek ( Holidays[i] ) in 2 to 6 and
    Holidays[i] in start to end then Hol:=Hol+1 );
    
    Weeks + Days  Hol
    Code:
    //Holiday Listing formula to go into the report header of the report.
    BeforeReadingRecords;
    DateVar Array Holidays := [
    Date (2003,12,25), 
    Date (2003,12,31) 
    ];
    0
    Code:
    //hours
    WhileReadingRecords;
    NumberVar Days := {@days}; // The field that calculates your business days
    TimeVar SetStart := TimeValue( "8:00"); // The start of your work day
    TimeVar SetEnd := TimeValue("20:30"); // The end of your work day
    TimeVar StartTime := TimeValue({@Adj Start});// The data field that holds your Start
    Time
    TimeVar EndTime := TimeValue({@Adj End}); // The data field that holds your End Time
    
    Days * ((SetEnd - SetStart) / 3600)
    - ((SetEnd - EndTime) / 3600)
    - ((StartTime - SetStart) / 3600)

    Code:
    // Adj Start 
    WhileReadingRecords;
    Local DateTimeVar AdjDateTime := {PROBSUMMARYM1.OPEN_TIME}; // Starting date 
    
    TimeVar SetStart := TimeValue( "8:00"); // The start of your work day 
    TimeVar SetEnd := TimeValue("20:30"); // The end of your work day 
    DateVar Array Holidays;
    // If the start date is a holiday or Saturday or Sunday,
    // move it back to 8:30 PM on the previous day, until
    // it's not a holiday, Saturday or Sunday
    while Date (AdjDateTime) in Holidays or
    DayOfWeek (AdjDateTime) in [ 1, 7 ] do
    AdjDateTime := DateTime (Date (AdjDateTime - 1), SetEnd);
    if Time (AdjDateTime) < SetStart then
    AdjDateTime := DateTime (Date (AdjDateTime), SetStart);
    
    if Time (AdjDateTime) > SetEnd then AdjDateTime := DateTime (Date (AdjDateTime), SetEnd);
    AdjDateTime

    Code:
    // Adj End
    WhileReadingRecords;
    Local DateTimeVar AdjDateTime := {PROBSUMMARYM1.CLOSE_TIME}; // Ending date
    TimeVar SetStart := TimeValue( "8:00"); // The start of your work day
    TimeVar SetEnd := TimeValue("20:30"); // The end of your work day
    DateVar Array Holidays;
    
    // If the end date is a holiday or Saturday or Sunday,
    // move it forward to 8 AM on the next day, until
    // it's not a holiday, Saturday or Sunday
    while Date (AdjDateTime) in Holidays or
    DayOfWeek (AdjDateTime) in [ 1, 7 ] do
    AdjDateTime := DateTime (Date (AdjDateTime + 1), SetStart);
    
    if Time (AdjDateTime) < SetStart then
    AdjDateTime := DateTime (Date (AdjDateTime), SetStart);
    
    if Time (AdjDateTime) > SetEnd then
    AdjDateTime := DateTime (Date (AdjDateTime), SetEnd);
    AdjDateTime
    Note: Extracted from Ken Hamady site except for the AdjEnd and AdjStart formulas.

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center