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,

Hmm, not sure I qualify as a geek, but here goes:

Local DateTimeVar d1 := {CallLog.RecvdDate};
Local DateTimeVar d2 := if isnull({CallLog.Closeddate})
then currentdatetime else
{CallLog.Closeddate}; ;

local numbervar age :=
DateDiff ("s", d1, d2) -
-
DateDiff ("ww", d1, d2, crSaturday) * 86400 -
DateDiff ("ww", d1, d2, crSunday) * 86400
;
age/ 36000

There are 36,000 seconds in a 10 hour day. There are 86400 seconds in a 24 hour day.

You want to subtract the entire weekend.

I am not sure why you enclosed this in cdbl as the result of the formula is a number.

Also, I added an isnull check on your closed date so that if it is null it gives you the age using now.

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

 

[rosemaryl]

Dear Proflola,

Sorry, after posting I started to wonder exactly what it is you are trying to accomplish.

Also, I missed the part on your error message.

If you are getting datetime required and it is stopping at the field you defined for your variables, are you sure that the received date and closed date fields are datetime fields? If not, then that is the problem.

What is the field type and data in those two fields.

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 think you're on the right track. My problem is that I have separate date and time fields. I have both a RecvdDate and RecvdTime field and also a ClosedDate and ClosedTime field.

My brain is fried on this one. You sure seem like a "geek" to me.

Any help you can give me would be most appreciated.

Thanks,

Sandy

 

[rosemaryl]

Dear Proflola,

I prefer to think of myself as a professional.

To combine a date and time field you can try the following:

cdate({Table.Date}) + Ctime({Table.Time})

This will return a valid date time field.

regard,

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, you are definitely an "expert".

Here's what I tried, but I still get the Date/Time error message. The Closed Date and Closed Time fields are strings as are the Recved Date and Recvd Time fields.

Local DateTimeVar d1:={CallLog.RecvdDate}+{CallLog.RecvdTime};
Local DateTimeVar d2 := if isnull({CallLog.ClosedDate}+{CallLog.ClosedTime})
then currentdatetime else
{CallLog.ClosedDate} + " "+{CallLog.ClosedTime}; ;

local numbervar age :=
(DateDiff ("s", d1, d2) -

DateDiff ("ww", d1, d2, crSaturday) * 86400 -
DateDiff ("ww", d1, d2, crSunday)) * 86400;age/ 36000

 

[Turkbear]

Hi,
To handle strings as Date and Time fields you must convert them first..Look at the CDate and CTime ( and also CDateTime) functions in Crystal to pre-process your data fields before using the functions like DateDiff..







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

 

[rosemaryl]

Dear Proflola,

Turkbear is correct. If you look at my suggestion you will see that what I posted was:

To combine a date and time field you can try the following:

cdate({Table.Date}) + Ctime({Table.Time})

This will return a valid date time field.

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]

I feel like an idiot, but here goes...

I'm not sure how to use the Cdate/Ctime function in the above example.

Egads and I thought I was beginning to have a handle on this. Thank you all for your patience.

If I get this one formula worked out, it'll make it possible to run numerous reports correctly.

Again, thank you to all you smart folks out there.

 

[rosemaryl]

Dear Proflola,

It is just a function.

Change your code to this:

Local DateTimeVar d1 := cdate({CallLog.RecvdDate}+{CallLog.RecvdTime});
Local DateTimeVar d2 := if isnull({CallLog.ClosedDate}) 
                        then currentdatetime else
                        Cdate({CallLog.ClosedDate} + {CallLog.ClosedTime});  

local numbervar age :=
(DateDiff ("s", d1, d2) -

   DateDiff ("ww", d1, d2, crSaturday) * 86400 -
   DateDiff ("ww", d1, d2, crSunday)) * 86400;
age/ 36000

hth,

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]

Good news! When I checked the above formula I get NO errors.

Bad news! When I try to run the report, I get the following message regarding "Local DateTimeVar d1:"

"Bad date format string". Now what? Am I still doing something wrong?


...Still begging for help, Sandy

 

[Turkbear]

Hi,
What is the actual output format of your
{CallLog.RecvdDate} and {CallLog.RecvdTime} fields..
To check, create a formula that is just the
{CallLog.RecvdDate}+{CallLog.RecvdTime} part of Rosmary's formula and show us the output for a typical record..






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

 

[proflola]

Here's the format that appears

"2004-09-03 10:39:22"

Any suggestions???

Boy are you guys/gals good!

--Sandy

 

[lbass]

Shouldn't Ro's formula be:

Local DateTimeVar d1 := cdate({CallLog.RecvdDate})+ctime({CallLog.RecvdTime});
Local DateTimeVar d2 := if isnull({CallLog.ClosedDate})
then currentdatetime else
Cdate({CallLog.ClosedDate}) + ctime({CallLog.ClosedTime});

local numbervar age :=
(DateDiff ("s", d1, d2) -

DateDiff ("ww", d1, d2, crSaturday) * 86400 -
DateDiff ("ww", d1, d2, crSunday)) * 86400;
age/ 36000

-LB

 

[proflola]

LB,

When I run it with your correction at least I don't get an error message, however the result is a number such as:

288,626.40

 

[proflola]

Here's a partial review of the results.

ClosedDate ClosedTime RecvdDate RecvdTime 10HrDays
2005-09-28 14:11:19 2005-09-03 10:39:22 288,626.40
2005-05-12 14:34:47 2005-05-09 16:00:52 609,684.00

 

[rosemaryl]

Dear LB,

Good catch. I typed it correctly in my suggestion and then didn't when I retyped her formula.

Dear Perforla,

When I do the calculation with your sample data I get:

43.55 hours.

Here is exactly what I entered:

Local DateTimeVar d1 := cdate('2005-09-03') + ctime('10:39:22');
Local DateTimeVar d2 := cdate('2005-09-28') + ctime('14:11:19');  

local numbervar age :=
DateDiff ("s", d1, d2) -

   DateDiff ("ww", d1, d2, crSaturday) * 86400 -
   DateDiff ("ww", d1, d2, crSunday) * 86400;
age := age/ 36000;
age

I think it had something to do with your original parens. When I retype the formula in Crystal as if I was going to use it, the formula changed slightly.

Hth,

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,

If I place the exact date and times in the formula like you did, I also come up with hours. However, it doesn't work with the rest of the data. I must be missing something. What would the correct statement be? My results:

ClosedDate ClosedTime RecvdDate RecvdTime 10HrDays

2005-09-28 14:11:19 2004-09-03 10:39:22 69,288,626.40

2005-05-12 14:34:47 2005-05-09 16:00:52 609,684.00

2005-05-12 12:48:35 2005-05-12 11:57:11 7,401.60

2005-05-12 14:51:19 2005-05-12 12:55:53 16,622.40

2005-05-16 15:21:58 2005-05-13 13:39:08 429,525.60

2005-05-25 13:00:01 2005-05-17 16:58:59 1,417,106.40

2005-05-26 08:46:14 2005-05-25 19:27:46 114,979.20

2005-06-02 15:08:49 2005-06-02 14:25:19 6,264.00

2004-08-20 10:16:56 2004-08-20 05:27:57 41,613.60

2004-08-24 12:31:43 2004-08-23 08:36:41 241,204.80

2004-10-15 13:01:11 2004-10-15 11:58:09 9,076.80

2005-01-24 14:28:17 2005-01-18 11:57:08 1,058,563.20

 

[rosemaryl]

Dear Proflola,

Did you replace your original formula with my new updated formula?

Local DateTimeVar d1 := cdate({CallLog.RecvdDate})+ctime({CallLog.RecvdTime});
Local DateTimeVar d2 := if isnull({CallLog.ClosedDate}) 
                        then currentdatetime else
                        Cdate({CallLog.ClosedDate}) + ctime({CallLog.ClosedTime});  
local numbervar age :=
DateDiff ("s", d1, d2) -

   DateDiff ("ww", d1, d2, crSaturday) * 86400 -
   DateDiff ("ww", d1, d2, crSunday) * 86400;
age := age/ 36000;
age

The second question would be, if you test with a new formula, with just this portion of the code, what is returned?

cdate({CallLog.RecvdDate})+ctime({CallLog.RecvdTime})

If the result is not a true datetime field (you will know because you can right click on the field and you will get DateTime format options) then that is the problem.

The issue is, that I don't know what database you are reporting on and what the true format of the fields are.

Some info will help.

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]

Here's the code I have now and still come up with a number format.


Local DateTimeVar d1 := cdate({CallLog.RecvdDate}) + ctime({CallLog.recvdTime});
Local DateTimeVar d2 := cdate({CallLog.ClosedDate}) + ctime({CallLog.ClosedTime});

local numbervar age :=
DateDiff ("s", d1, d2) -

DateDiff ("ww", d1, d2, crSaturday) * 86400 -
DateDiff ("ww", d1, d2, crSunday) * 86400;
age := age/ 36000;
age