Date and Time Calcs 1

[gennaroalpha7]

Hello -

I need to write a report that calculates the amount of time expired between two 'dates' and two 'times'.

Here is my date fields...CallLog.RecvDate...CallLog.RecvdTime...and...CallLog.ClosedDate...CallLog.ClosedTime = Total Time Expired (in Hours and Minutes).

Is there a way to build a formula or function that will do this? Does one already exist that I can download?

Thank you.
Any and all suggestions are welcome.

Alpha7

 

[pmax9999]

Try this:

DateDiff("s", DateTime(Date({CallLog.RecvDate}), Time({CallLog.RecvdTime})), DateTime(Date({CallLog.ClosedDate}), Time({CallLog.ClosedTime})))

This will return the number of seconds between the two dates. You can then manipulate the result to turn it into Hours, days or whatever time unit is required.

Cheers
Pete

 

[gennaroalpha7]

Thanks Pete, I'll try this and see how it goes.

Is this formula entered into CR exactly as it's shown?

Alpha7

 

[gennaroalpha7]

Hi -

When the above syntax is used it reads 'A boolean is required here'. What is a boolean and how can I convert this to hours and minutes.

Any help is appreciated.

Thanks.

 

[pmax9999]

What is highlighted in the formula when it gives the error, ie, where is "here"?

Please confirm the data types for the 4 fields, {CallLog.RecvDate}, {CallLog.RecvdTime}, {CallLog.ClosedDate} and {CallLog.ClosedTime} and provide some sample data.

As for converting seconds to hours and minutes, the following formula will do that.

WhilePrintingRecords;
Local NumberVar seconds := [[i]formula that returns number of seconds[/i]];

ToText(Truncate(seconds/3600), '#') + ' hour(s), ' + ToText(Remainder(seconds, 3600)/60, '#') + ' minute(s)'

Obviously, before implementing the conversion from seconds to hours and minutes, we need to be able to calculate the number of seconds. Also, please explain how you want the conversion to deal with any seconds left over after calculating hours and minutes. For example do you want 4:21:30 to include the extra 30 seconds, ignore the extra 30 seconds (and either round off, up or down), or convert the seconds to a decimal (in this example 21.5 minutes.

Cheers

Pete

 

[gennaroalpha7]

Hello Pete -

To answer your question - it highlights the entire formula. I will show you the code I currently have - so far...

not ({Asgnmnt.GroupName} in ["ATM Services", "ECC", "EIS", "NEO", "Telecom"]) and
not ({CallLog.CallType} in ["ACD", "ATM", "ATM Debit Card", "ATM Debit Card Maint", "ATM Night Bag", "Audit/Jrnl Prnt", "Backup Failed", "Balance Detail", "Battery", "Blackberry", "Call Stack Error", "Card Reader", "Cash Check", "Cash Dispenser/TAU", "Cell Phone", "CIF/Data Warehouse", "Cisco Hardware", "Cisco UCCE (ACD)", "Combined Maintenance", "Comm Errors", "Communications", "Connection", "Customer/Account Inquiry", "Dataset", "Depository", "Dispenser", "Envelope Dispenser", "Enviroment", "Enviroment*", "Environment", "Environment.", "File Restore", "General Ledger", "Jet Forms", "Keyboard", "Keys/Combo", "Line Circuit", "Missing", "Monitor", "Mouse", "Multi Transaction", "No Activity", "Password -FC8", "Password-App", "Password-CACS", "Password-Evision", "Password-Lan", "Password-Lan.", "Pin Pad", "Printer", "Printing", "Printing.", "Procedural", "Question", "Receipt Printer", "Reg CC", "Slow Response", "Space Alloc", "Space Alloc.", "Supervisor Override", "T1", "Teller Capture", "Transfering data", "Unavail.", "Unavailable", "Unavailable..", "Unavailable...", "Unavailable....", "Vandalism", "Vault", "Vendor", "Vendor Meet", "Voicemail", "VPN"]) and
not ({CallLog.Cause} in ["Cics errors", "Net-Circuit Err", "Net-Hardw Err", "Net-System Err", "No Activity", "Power Outage", "Procedural / User Error", "Procedural err"]) and
{CallLog.RecvdDate} in "2013-08-01" to "2013-08-31"

Pete, in answering your question - please convert to decimal to as accurate as possible. This seems to be the most accurate.

I am trying to attach a file or screen shot of my report...how is that accomplished?

Thank you.

Alpha7

 

[gennaroalpha7]

Hello -

I have included the code that you gave me into what I currently have...please review it.

not ({Asgnmnt.GroupName} in ["ATM Services", "ECC", "EIS", "NEO", "Telecom"]) and
not ({CallLog.CallType} in ["ACD", "ATM", "ATM Debit Card", "ATM Debit Card Maint", "ATM Night Bag", "Audit/Jrnl Prnt", "Backup Failed", "Balance Detail", "Battery", "Blackberry", "Call Stack Error", "Card Reader", "Cash Check", "Cash Dispenser/TAU", "Cell Phone", "CIF/Data Warehouse", "Cisco Hardware", "Cisco UCCE (ACD)", "Combined Maintenance", "Comm Errors", "Communications", "Connection", "Customer/Account Inquiry", "Dataset", "Depository", "Dispenser", "Envelope Dispenser", "Enviroment", "Enviroment*", "Environment", "Environment.", "File Restore", "General Ledger", "Jet Forms", "Keyboard", "Keys/Combo", "Line Circuit", "Missing", "Monitor", "Mouse", "Multi Transaction", "No Activity", "Password -FC8", "Password-App", "Password-CACS", "Password-Evision", "Password-Lan", "Password-Lan.", "Pin Pad", "Printer", "Printing", "Printing.", "Procedural", "Question", "Receipt Printer", "Reg CC", "Slow Response", "Space Alloc", "Space Alloc.", "Supervisor Override", "T1", "Teller Capture", "Transfering data", "Unavail.", "Unavailable", "Unavailable..", "Unavailable...", "Unavailable....", "Vandalism", "Vault", "Vendor", "Vendor Meet", "Voicemail", "VPN"]) and
not ({CallLog.Cause} in ["Cics errors", "Net-Circuit Err", "Net-Hardw Err", "Net-System Err", "No Activity", "Power Outage", "Procedural / User Error", "Procedural err"]) and
{CallLog.RecvdDate} in "2013-08-01" to "2013-08-31" and DateDiff("s", DateTime(Date({CallLog.RecvdDate}), Time({CallLog.RecvdTime})), DateTime(Date({CallLog.ClosedDate}), Time({CallLog.ClosedTime})))

Thanks

Alpha7

 

[pmax9999]

Where have you added the code - is it in the Record selection?

The code is intended to be a stand-alone formula that return the number of seconds between 2 date/times. The way you have used it needs to include and "=", ">", "<" and a number of seconds.

Perhaps if you explain what you are trying to achieve we may be better placed to assist.

Pete

 

[gennaroalpha7]

Hello -

I have entered all the code, including the (10 Sep 13 18:40) code you gave me into the record selection. What I am trying to accomplish is to figure the resolution time of each ticket, or what the resolve time of each ticket was. For instance, I have a ticket received date/time of 09/12/2013 and 10:25:33am and ticket resolve date/time of 09/16/2013 and 7:20:10pm. From this I would like to figure the hours and minutes that it took to resolve each ticket. I am still learning crystal. Thanks for your help.

Alpha7

 

[gennaroalpha7]

Hello -

I have created a, from the code above, formula under 'Formula Fields' called '@dateTime' and inserted it into my report canvas. It has returned the number of 'seconds'. The Datatypes for the four fields (CallLog.RecvdDate, CallLog.RecvdTime, CallLog.ClosedDate, and CallLog.ClosedTime - these are 'strings'.

The formula for converting seconds to hours abd minutes:

WhilePrintingRecords;
Local NumberVar seconds := [formula that returns number of seconds];

ToText(Truncate(seconds/3600), '#') + ' hour(s), ' + ToText(Remainder(seconds, 3600)/60, '#') + ' minute(s)'

Where is this formula placed? Thanks for your help.

Alpha7

 

[gennaroalpha7]

Hello -

The @datetime formula is coming up with a 'Bad Date Format String', but when I check the formula no errors show up. Any Ideas as to what is causing this? Thanks. Here is the formula...

DateDiff("s", DateTime(Date({CallLog.RecvdDate}), Time({CallLog.RecvdTime})), DateTime(Date({CallLog.ClosedDate}), Time({CallLog.ClosedTime})))

Thank you.

Alpha7

 

[gennaroalpha7]

I forgot to add that this part of the formula is highligted with the 'Bad Date Format String' error...

Date({CallLog.ClosedDate})

Thanks.

Alpha7

 

[gennaroalpha7]

Hi -

I have created a stand-alone formula called '@DateTime' with the following code. This is based on the code above, post 10 Sep 13 18:40

DateDiff("s", DateTime(Date({CallLog.RecvDate}), Time({CallLog.RecvdTime})), DateTime(Date({CallLog.ClosedDate}), Time({CallLog.ClosedTime})))/60

But, I need this to be more accurate. How can I combine this formula with the one from post 11 Sep 13 19:21 above. Does this code need to be stand-alone also?

WhilePrintingRecords;
Local NumberVar seconds := [formula that returns number of seconds];

ToText(Truncate(seconds/3600), '#') + ' hour(s), ' + ToText(Remainder(seconds, 3600)/60, '#') + ' minute(s)'

Thanks.
Any help would be appreciated.

 

[pmax9999]

[Code {@Diff-Hours_Min}]
WhilePrintingRecords;
Local NumberVar seconds := DateDiff("s", DateTime(Date({CallLog.RecvDate}), Time({CallLog.RecvdTime})), DateTime(Date({CallLog.ClosedDate}), Time({CallLog.ClosedTime})));

ToText(Truncate(seconds/3600), '#') + ' hour(s), ' + ToText(Remainder(seconds, 3600)/60, '#.##') + ' minute(s)'
[/Code]

This is intended to be a stand-alone formula. Place it in the detais section and for each record it will tell you the number of hours and minutes (to 2 decimal places between the received date/time and closed date/time.

So, for a record with a received date/time of today at 7:00:00 and a closed date/time of today at 9:10:30, the formula would return "2 hour(s), 10.50 minute(s)". Is this what you want?

Pete

 

[gennaroalpha7]

Hi Pete -

Yes, But when I run the report I am getting 'Bad Date Format String'. I have highlighted the area....

WhilePrintingRecords;
Local NumberVar seconds := DateDiff("s", DateTime(Date({CallLog.RecvDate}), Time({CallLog.RecvdTime})), DateTime[highlight #FCE94F](Date({CallLog.ClosedDate}),[/highlight] Time({CallLog.ClosedTime})));

ToText(Truncate(seconds/3600), '#') + ' hour(s), ' + ToText(Remainder(seconds, 3600)/60, '#.##') + ' minute(s)'

Thanks.

 

[pmax9999]

Then post a sample of the data from that field.

 

[gennaroalpha7]

Hi -

Heres some data that's in that field....

Closed Date

2013-08-15

2013-08-23

Thanks.

 

[pmax9999]

Based on my testing Crystal will correctly convert a string of "2013-08-15" to a date of 15 Aug 2013.

The only thing that I can think of is that there is date in the ClosedDate column that can't be converted to a data, which is of course the problem with such a poorly designed database that stores dates and times as strings.

Try amending the formula as follows:

WhilePrintingRecords;
Local NumberVar seconds := 0;

If      IsDate({CallLog.ClosedDate})    and
        IsDate({CallLog.RecvDate})      and
        IsTime({CallLog.ClosedTime})    and
        IsTime({CallLog.RecvdTime})
Then    seconds := DateDiff("s", DateTime(Date(Trim({CallLog.RecvDate})), Time({CallLog.RecvdTime})), DateTime(Date(Trim({CallLog.ClosedDate})), Time({CallLog.ClosedTime})))
Else    seconds := 0;

ToText(Truncate(seconds/3600), '#') + ' hour(s), ' + ToText(Remainder(seconds, 3600)/60, '#.##') + ' minute(s)'

If it encounters any string data that can not be interpreted as a valid date or time, the difference returned will be zero.

Pete

 

[gennaroalpha7]

Pete -

Thank you very much. Is there a book on Crystal Syntax programming that you can possibly recommend?

Have a good one.

Alpha7

 

[pmax9999]

Not really, there are lots of books around but there aren't any I have found that I was particularly impressed with. My suggestions for learning Crystal would be:
[ul]
[li]Take a look at some of Ken Hamady's publications, such as this one here. I purchased a couple of his in the early days and found them to be very helpful and good value;[/li]
[li]Read the question and answer posts on this site regularly;[/Li]
[Li]Use the CR Help files to learn about areas you do not fully understand; and[/li]
[Li]Subscribe to Ken Hamady's blog. He provides very helpful hints.[/li]
[/ul]

As you will have discovered, there are some very experienced Crystal Reporting professionals on this site who are prepared to invest a considerable amount of time and effort to assist people. Just make sure you provide as much information as possible when posting and invariably someone will be willing to help.

Cheers
Pete