Combine Date and Time fields HELP 3

[proflola]

Below is the formula I'm trying to use to calculate the difference from the Recieved Date and Time to the Closed Date and Time, however it has to be based on 10 hour days excluding weekends. I get a message saying "date-time is required" when I check the formula.

Local DateTimeVar d1 := {CallLog.RecvdDate};
Local DateTimeVar d2 := {CallLog.Closeddate};
cdbl(DateDiff ("d", d1, d2) -
DateDiff ("ww", d1, d2, crSaturday) -
DateDiff ("ww", d1, d2, crSunday))*10

Any geeks out there up for the challenge???

I'm so close to a solution.....HELP!

Sandy

 

[rosemaryl]

Dear Proflola,

A. I don't understand what you mean when you say still come up with a number format. What are you expecting? The formula will result in a number. For example: 43.55.

B. You did not answer my second question. I suggest that rather then printing what is in your fields, that you do the conversion portion that the formula is using and see the result. Because, until we know what these two return we are flying blind.

Create two new formulas:

//Received Date Time:

cdate({CallLog.RecvdDate}) + ctime({CallLog.recvdTime})
//end

//Closed Date Time
if isnull({CallLog.ClosedDate})
then currentdatetime else
Cdate({CallLog.ClosedDate}) + ctime({CallLog.ClosedTime})
//end

Those two will be what the formula is using so.... we need to see what those two pieces are doing. I think you are going to see a very interesting date.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[proflola]

Rosemary,

I just sent you an email. Here is the result of your suggestion above:

Closed Date Time Received Date Time 10HrDays
9/28/2005 2:11:19 PM 9/3/2004 10:39:22 AM 667.55

5/12/2005 2:34:47 PM 5/9/2005 4:00:52 PM 7.06

5/12/2005 12:48:35 PM 5/12/2005 11:57:11 AM 0.09

5/12/2005 2:51:19 PM 5/12/2005 12:55:53 PM 0.19

5/16/2005 3:21:58 PM 5/13/2005 1:39:08 PM 2.57

5/25/2005 1:00:01 PM 5/17/2005 4:58:59 PM 14.00

5/26/2005 8:46:14 AM 5/25/2005 7:27:46 PM 1.33

6/2/2005 3:08:49 PM 6/2/2005 2:25:19 PM 0.07

 

[Turkbear]

Hi,
It appears that the formula is working..at least for this one:
6/2/2005 3:08:49 PM 6/2/2005 2:25:19 PM 0.07

There are .07 10 Hour days between 2:25:19 and 3:08:49
( if my math is correct.. )





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rosemaryl]

Dear Proflola,

I received your email, but we should keep the topic going here. I was out of the office yesterday, hence the delay in response. I will be out of the office the rest of today too.

Your email states that you need the business hours (based upon a 10 hour day). This was not exactly what you asked for in the beginning.

I’m trying to figure a way to do something similar using Crystal Reports since you can access the reports directly using HEAT.

Regular hours need to be calculated during the 10 hour workdays
Off -work hours need to be subtracted if the calls run over the 10 hour work period
Weekends need to be excluded

I think you need the following formula:

DatetimeVar FDay:= cdate({CallLog.RecvdDate}) + ctime({CallLog.recvdTime})
;
DatetimeVar LDay:= if isnull({CallLog.ClosedDate}) 
then currentdatetime else
Cdate({CallLog.ClosedDate}) + ctime({CallLog.ClosedTime}); 

TimeVar BusinessStartTime:= time(7,00,0); //ENTER START TIME;
//example for 7:30 AM: time(7,30,0)
TimeVar BusinessEndTime:= time(17,0,0); //ENTER Business Day END TIME;
//example for 5:00 PM: time(17,0,0)
NumberVar BSTime;
NumberVar BETime;
Numbervar Days;
Numbervar Weekends;
Numbervar Finaldays;
DateVar StartDate;
DateVar EndDate;
NumberVar halfdays;
NumberVar fulldays;
NumberVar hours;
//If first day or last day is null or less than null assign hours to 0 and finish the formula
if FDay <= Date(0,0,0) or LDay <=Date(0,0,0) then
hours:= 0
else if
//If call opened outside business hours then straight time ... remove if not what you want.
 
(time(fday) > BusinessEndTime and time(lday) > BusinessEndTime and ((dayofweek(fday) = dayofweek(lday))
 or dayofweek(fday) = [6] and datediff("d",fday,lday) <= 2)
)
then
hours:=  datediff("s",fday, lday) /3600

//end new section
//Else assign hours
else (
//Assign Business Start Time and Business End Time
BSTime:= hour(BusinessStartTime) +
(minute(BusinessStartTime) +second(BusinessStartTime));
BETime:= hour(BusinessEndTime) + (minute(BusinessEndTime)+second(BusinessEndTime)) ;
//Assign First Day and Last Day
//Determine whether FDay falls within
//start time and end time

if hour(FDay) in BSTime to BETime then
FDay:= FDay
else if hour(FDay) > BETime then
FDay:= datetime(date(FDay), BusinessEndTime)
else if hour(FDay) < BSTime then
FDay:= datetime(date(FDay), BusinessStartTime);
//Determine whether LDay falls within start
//time and end time
if hour(LDay) in BSTime to BETime then
LDay:= LDay
else if hour(LDay) > BETime then
LDay:= datetime(date(LDay), BusinessEndTime)
else if hour(LDay) < BSTime then
LDay:= datetime(date(LDay), BusinessStartTime);


//Assign StartDate and EndDate
//if the first day falls on a weekend,
//StartDate is equal to the following Monday
//for calculation reasons
If DayOfWeek(FDay) = 7 Then
StartDate := date(FDay) + 2
Else
If DayOfWeek(FDay) = 1 Then
StartDate := date(FDay) + 1
Else
StartDate:=date(FDay);
//if the last day falls on a weekend,
//EndDate is equal to the following Monday
//for calculation reasons
If DayOfWeek(LDay) = 7 Then
EndDate := date(LDay) + 2
Else If DayOfWeek(LDay) = 1 Then
EndDate := date(LDay) + 1
Else
EndDate := date(LDay);
//Calculate days (including First day and Last day)
Days:= (EndDate - StartDate)+1;
//Calculate Weekends
if Days >= 7 then
WeekEnds := (Round((Days/7),0))*2
else if DayOfWeek(StartDate) > DayOfWeek(EndDate) then
WeekEnds := 2
else
WeekEnds := 0;
//Calculate FinalDays
//If the Last Day is on a weekend then
//FinalDays subtract the weekend days
If DayOfWeek(LDay) = 7 then
FinalDays:= FinalDays - 1;
If DayOfWeek(LDay) = 1 then
FinalDays:= FinalDays - 2;
//Assign FinalDays to Days - Weekends
FinalDays:= Days - WeekEnds;
//Calculate Hours
//If there is less than 2 days, hours = last
//days hour - first days hour
If FinalDays <= 1 then
(if hour(LDay) <= BETime then
hours:= (time(LDay) - time(FDay))/3600
else
hours:= (BusinessEndTime - time(FDay))/3600)
//Else hours = how many hours on the two half
//days + how many hours for the full days
Else (
halfdays:= ((BusinessEndTime - time(FDay)) /3600 +
(time(LDay) - BusinessStartTime)
/3600);
fulldays:= (FinalDays-2) * 9;
hours:= halfdays + fulldays);  
) ;
 
hours

Try that and see if it works.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[proflola]

Ro,

It seems to work, except for the error message I get regarding ClosedDate (4th line) "bad formula" message. After I close the message the data seems to be correct.

It's so close to being solved! I'm sure it's something very simple now. I am so impressed with the knowledge on this forum.

 

[proflola]

I feel like I'm becoming a pest, but I have one more question...

Is it possible to have the result appear in a HH:MM format rather than a number format?

The Excel NetworkDAys function returns the value in a time format which is really what the client needs.

Any thoughts on it?

--Sandy

 

[rosemaryl]

Dear Proflola,

No problem. Here is a formula that will display as a time string HH:MM:SS:

//{@t} is your hours formula!
Local Numbervar TotalSec :=  {@t}*3600  ;
Local NumberVar Hours   := Truncate ( TotalSec/ 3600); 
Local NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60); 
Local NumberVar Seconds := Remainder ( TotalSec , 60); 

Totext ( Hours,  '00', 0,'') + ':'+ 
Totext ( Minutes,'00', 0,'') + ':'+ 
Totext ( Seconds,'00', 0,'')

regards,

ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[proflola]

Still get the "Bad Format" error when I try to run the report. It seems to be in connection with the Closed Date and Time. If I plug in an actual date and time, it works. I can't figure out what's wrong.

Also, where do I place the conversion formula that you so kindly posted here.

--Sandy

 

[rosemaryl]

Perflola,

Please copy and paste the exact formula from your report here. My last formula would be what was actually placed on the report.

Also, what database are you using?

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[proflola]

Ro,

Here's the code that I have in the report. I believe the HEAT database is either in SQL or Access. I use ODBC to connect to it. I also have an Access database linked via ODBC to the database so that I could get a handle on what data is available.

--Sandy


DatetimeVar FDay:= cdate({CallLog.RecvdDate}) + ctime({CallLog.recvdTime})
;
DatetimeVar LDay:=
Cdate({CallLog.ClosedDate}) + ctime({CallLog.ClosedTime});

TimeVar BusinessStartTime:= time(7,00,0); //ENTER START TIME;
//example for 7:30 AM: time(7,30,0)
TimeVar BusinessEndTime:= time(17,0,0); //ENTER Business Day END TIME;
//example for 5:00 PM: time(17,0,0)
NumberVar BSTime;
NumberVar BETime;
Numbervar Days;
Numbervar Weekends;
Numbervar Finaldays;
DateVar StartDate;
DateVar EndDate;
NumberVar halfdays;
NumberVar fulldays;
NumberVar hours;
//If first day or last day is null or less than null assign hours to 0 and finish the formula
if FDay <= Date(0,0,0) or LDay <=Date(0,0,0) then
hours:= 0
else if
//If call opened outside business hours then straight time ... remove if not what you want.

(time(fday) > BusinessEndTime and time(lday) > BusinessEndTime and ((dayofweek(fday) = dayofweek(lday))
or dayofweek(fday) = [6] and datediff("d",fday,lday) <= 2)
)
then
hours:= datediff("s",fday, lday) /3600

//end new section
//Else assign hours
else (
//Assign Business Start Time and Business End Time
BSTime:= hour(BusinessStartTime) +
(minute(BusinessStartTime) +second(BusinessStartTime));
BETime:= hour(BusinessEndTime) + (minute(BusinessEndTime)+second(BusinessEndTime)) ;
//Assign First Day and Last Day
//Determine whether FDay falls within
//start time and end time

if hour(FDay) in BSTime to BETime then
FDay:= FDay
else if hour(FDay) > BETime then
FDay:= datetime(date(FDay), BusinessEndTime)
else if hour(FDay) < BSTime then
FDay:= datetime(date(FDay), BusinessStartTime);
//Determine whether LDay falls within start
//time and end time
if hour(LDay) in BSTime to BETime then
LDay:= LDay
else if hour(LDay) > BETime then
LDay:= datetime(date(LDay), BusinessEndTime)
else if hour(LDay) < BSTime then
LDay:= datetime(date(LDay), BusinessStartTime);


//Assign StartDate and EndDate
//if the first day falls on a weekend,
//StartDate is equal to the following Monday
//for calculation reasons
If DayOfWeek(FDay) = 7 Then
StartDate := date(FDay) + 2
Else
If DayOfWeek(FDay) = 1 Then
StartDate := date(FDay) + 1
Else
StartDate:=date(FDay);
//if the last day falls on a weekend,
//EndDate is equal to the following Monday
//for calculation reasons
If DayOfWeek(LDay) = 7 Then
EndDate := date(LDay) + 2
Else If DayOfWeek(LDay) = 1 Then
EndDate := date(LDay) + 1
Else
EndDate := date(LDay);
//Calculate days (including First day and Last day)
Days:= (EndDate - StartDate)+1;
//Calculate Weekends
if Days >= 7 then
WeekEnds := (Round((Days/7),0))*2
else if DayOfWeek(StartDate) > DayOfWeek(EndDate) then
WeekEnds := 2
else
WeekEnds := 0;
//Calculate FinalDays
//If the Last Day is on a weekend then
//FinalDays subtract the weekend days
If DayOfWeek(LDay) = 7 then
FinalDays:= FinalDays - 1;
If DayOfWeek(LDay) = 1 then
FinalDays:= FinalDays - 2;
//Assign FinalDays to Days - Weekends
FinalDays:= Days - WeekEnds;
//Calculate Hours
//If there is less than 2 days, hours = last
//days hour - first days hour
If FinalDays <= 1 then
(if hour(LDay) <= BETime then
hours:= (time(LDay) - time(FDay))/3600
else
hours:= (BusinessEndTime - time(FDay))/3600)
//Else hours = how many hours on the two half
//days + how many hours for the full days
Else (
halfdays:= ((BusinessEndTime - time(FDay)) /3600 +
(time(LDay) - BusinessStartTime)
/3600);
fulldays:= (FinalDays-2) * 9;
hours:= halfdays + fulldays);
) ;

hours

 

[rosemaryl]

Dear Proflola,

Are all your records closed? I believe the issue might be that some of the closed dates are null. In my original code I indicated to put:

DatetimeVar LDay:= if isnull({CallLog.ClosedDate}) 
then currentdatetime else
Cdate({CallLog.ClosedDate}) + ctime({CallLog.ClosedTime});

Please try it that way and see if you still get the error.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[proflola]

Ro,

I just tried it with the changed code and I still get the "Bad Format" error. I don't have a clue... any suggestions?

 

[rosemaryl]

Dear Proflola,

It may be that field can also contain a blank so change this bit of code as follows:

DatetimeVar LDay:= if isnull({CallLog.ClosedDate}) 
                   then currentdatetime 
                   else if {CallLog.ClosedDate} = ""
                   then currentdatetime
                   else Cdate({CallLog.ClosedDate}) + ctime({CallLog.ClosedTime});

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[proflola]

Ro,

I worked!!! Hooray! Now, all I have to do is place the conversion formula.

You are a genius!

--Sandy

 

[proflola]

Only one more and hopefully my last question...

Where do I place this hours conversion formula?

//{@t} is your hours formula!
Local Numbervar TotalSec := {@t}*3600 ;
Local NumberVar Hours := Truncate ( TotalSec/ 3600);
Local NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
Local NumberVar Seconds := Remainder ( TotalSec , 60);

Totext ( Hours, '00', 0,'') + ':'+
Totext ( Minutes,'00', 0,'') + ':'+
Totext ( Seconds,'00', 0,'')

 

[proflola]

HOOOOOOOORAY! The report is working!!! I haven't had a chance to check the calculations, but so far it looks terrific!

Ro and others--You are absolute geniuses! I can't thank you enough, (especially Ro) for all of your wonderful support and patience!

I've been running these reports for the Help Desk Manager each month using a combination of Access and Excel. I knew that if I could get the calculations to work in Crystal Reports/HEAT, the Help Desk Manager and the Director would be able to generate their own reports via HEAT (which is the way it's suppose to work).

Not being as familiar with Crystal Reports coding, I had no idea that it would require as much code as it did, so I'm so pleased that you were all SOOOO patient with me.

Again, THANKS!

Sandy

 

[rosemaryl]

Dear Sandy,

You are very welcome ...

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762