DateDiff Formulae

[ss85]

Hello,

I am new to this forum and to crystal reports. I have an issue that i encountered this morning and need to solve as soon as possible as i need to be able to run these reports quickly for the senior management.

I have two DateTime fields: Create_Date and End_Time. My service level agreement is setup for 2 days. So i need to a formulae to give the difference in days, hours, minutes and seconds when subtracting the End_time from Create_Date. It also needs to exclude weekends.

I am currently using these formulae:
1. NoOfDays formulae:
WhileReadingRecords;
Local DateVar Start := (date ({TMS_Task.Create_Date}));
Local DateVar End := (date ({TMS_Task.End_Time}));
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 ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1);

Hol


2. Formulae to subtract weekend
((datediff ("ww", {TMS_Task.Create_Date}, {TMS_Task.End_Time}) +
datediff ("ww", {TMS_Task.Create_Date}, {TMS_Task.End_Time})) +
{@NoOfDays})

3. datediff formulae:
datediff ("d", {TMS_Task.Create_Date}, {TMS_Task.End_Time})- {@Weekends}

Using the above formulae, the result is differene in days and it ignores the difference in hours and minutes and hence all our SLA which are missed by seconds, minutes and hours are neglected and only the one which are in days are taken into account.

For eg: Create_Date = 08/17/2012 2:18:34PM and End_Time = 08/21/2012 2:39:21PM, in this using my formulae the Ticket shows up as it has Met the SLA. however, in essence it has breeched or did not meet the SLA by 21 minutes.


Can someone help me out with this ASAP?

Thanks!!

 

[pmax9999]

Hi ss85

All you need to do is work in seconds rather than days, then use the total seconds to calculate days, hours minutes and seconds.

The following formula would calculate the difference bewteen the two date/time fields:

WhilePrintingRecords;

//Calculate Total Seconds
Local NumberVar T := DateDiff('s', {TMS_Task.Create_Date}, {TMS_Task.End_Time});

//Whole Days
Local NumberVar D := Truncate(T/86400);

// Hours
Local NumberVar H := Truncate((T - (D * 86400)) / 3600);

//Minutes
Local NumberVar M := Truncate((T - (D * 86400) - (H * 3600)) / 60);

// Seconds
Local NumberVar S := T - (D * 86400) - (H * 3600) - (M * 60);

ToText(D, '#') + ' Day(s) '    +
ToText(H, '#') + ' Hour(s) '   +
ToText(M, '#') + ' Minute(s) ' +
ToText(S, '#') + ' Second(s)'

This formula does not take into account weekends/public holidays, but that can be incorporated by reducing the total days accordingly.

Hope this helps.

Cheers,
Pete

 

[ss85]

hi thanks alot for your response. I will try this and let you know if this works for me.