Minutes to Days/Hours/Minutes 1

[NattyCat]

OK I fixed the "bad date format string" error - it WAS to do with null values. Here's a different problem.

using this forum, I found the following formula:

numberVar dur := 999; //replace the 999 with your field.
numberVar days;
numberVar hrs;
numberVar min;
stringVar result;

days := Truncate(dur/480);
hrs := Truncate(Remainder(dur,480)/60);
min := Remainder(Remainder(Remainder(dur,480),60),60);


result := totext(days, "0") + " d, " + totext(hrs, "0") + " h, " + totext(min,"0") + " m";

result

I need to use this formula, as I have a field which contains minutes, which I need to convert to days, hours and minutes.

The field that contains the minutes is derived from the following formulas:

@{Date Received}
datetime(date({CallLog.RecvdDate}) & " " & time({CallLog.RecvdTime}))

@Date Acknowledged
datetime(date({Asgnmnt.DATEACKNOW}) & " " & time({Asgnmnt.TIMEACKNOW}))

@{Difference between Rec and Ack in MINUTES}
datediff("n",{@DT Received},{@DT Acknowledged})

I then place the above formula in the conversion formula that I retrieved from this forum.

I get the following results:


Call ID Call Type Status Date Received Date Acknowledged Minutes Conversion
61294 Software Closed 05/01/2004 11:42 07/01/2004 14:23 3041 6 d, 2 h, 41 m Wrong
61329 Hardware Open 06/01/2004 09:17 07/01/2004 14:23 1746 3 d, 5 h, 6 m Wrong
61366 Telephone Closed 06/01/2004 12:18 07/01/2004 11:30 1392 2 d, 7 h, 12 m Wrong
61373 Software Closed 06/01/2004 13:12 07/01/2004 09:16 1204 2 d, 4 h, 4 m Wrong
61375 Software Closed 06/01/2004 14:09 07/01/2004 15:05 1496 3 d, 0 h, 56 m Wrong
61376 Software Closed 06/01/2004 14:09 07/01/2004 09:23 1154 2 d, 3 h, 14 m Wrong
61385 Software Closed 06/01/2004 15:21 07/01/2004 09:22 1081 2 d, 2 h, 1 m Wrong
61387 Software Closed 06/01/2004 15:33 07/01/2004 12:13 1240 2 d, 4 h, 40 m Wrong
61389 Software Closed 06/01/2004 15:45 07/01/2004 09:27 1062 2 d, 1 h, 42 m Wrong
61392 Software Closed 06/01/2004 16:08 07/01/2004 09:17 1029 2 d, 1 h, 9 m Wrong
61394 Telephone Closed 06/01/2004 16:23 07/01/2004 09:09 1006 2 d, 0 h, 46 m Wrong
61396 Software Closed 06/01/2004 16:35 07/01/2004 15:11 1356 2 d, 6 h, 36 m Wrong
61401 Request Closed 07/01/2004 08:53 07/01/2004 09:11 18 0 d, 0 h, 18 m Correct
61402 Telephone Closed 07/01/2004 09:02 07/01/2004 09:02 0 0 d, 0 h, 0 m Correct
61408 Software Closed 07/01/2004 09:45 07/01/2004 11:40 115 0 d, 1 h, 55 m Correct
61409 Printing PC Closed 07/01/2004 09:47 07/01/2004 11:47 120 0 d, 2 h, 0 m Correct
61417 Software Closed 07/01/2004 10:55 07/01/2004 11:38 43 0 d, 0 h, 43 m Correct
61422 Printing Closed 07/01/2004 11:14 07/01/2004 12:20 66 0 d, 1 h, 6 m Correct
61425 Request Closed 07/01/2004 11:40 07/01/2004 11:42 2 0 d, 0 h, 2 m Correct
61426 Request Closed 07/01/2004 11:42 07/01/2004 11:43 1 0 d, 0 h, 1 m Correct
61428 Software Closed 07/01/2004 12:40 07/01/2004 12:41 1 0 d, 0 h, 1 m Correct
61429 Software Closed 07/01/2004 12:50 07/01/2004 14:13 83 0 d, 1 h, 23 m Correct
61433 Software Closed 07/01/2004 14:37 07/01/2004 14:59 22 0 d, 0 h, 22 m Correct

I don't understand why some work fine, and some don't. It looks like it works fine when the number of minutes are within the same day, but as soon as the date changes, the conversion spews out random days, hours and minutes, and so does the datediff formula.

Help anyone?

 

[lupins46]

The problem is :
when calulating minutes you are getting the true total minutes difference between the two datetime values.

When converting the minutes to elapsed time you are saying that one day = 480 minutes (=8 hours).

If you want the true elapsed time then change 480 to 1440 (which is the acual number of minutes in a day).

 

[NattyCat]

Brilliant. That works. It brings up another issue though, one I thought was solved.

the Date Acknowledged field is sometimes null because a helpdesk call can be recieved, but not yet acknowledged. I wil also be calculating the time between the call being acknowledged, and resolved. A call may be acknowledged, but it may not have yet been resolved. Hence the null values.

The formula, which is used to concatenate the individual date and time fields and convert to a date time is as follows:

datetime(date({Asgnmnt.DATEACKNOW}) & " " & time({Asgnmnt.TIMEACKNOW}))


If I run the report, and keep the null values in the report - i get the error "bad date format string". I don't get this error if I limit the report to only bringing back records where the acknowledged field is populated.

If i add an extra line to the formula:

if not isnull({Asgnmnt.DATEACKNOW}) then
datetime(date({Asgnmnt.DATEACKNOW}) & " " & time({Asgnmnt.TIMEACKNOW}))


Then this has no effect.

I have also turned OFF the "convert database NULL values to default" option.

So, somehow, i need to convert the two string fields to datetime, leave them in the report, and STILL calcuate the difference between the two dates whether they are null or not.

Any clues?

 

[cwsstins]

I noticed you're using Crystal with HEAT. Do you have the "HFW" functions added? There are several functions starting with "HFW..."

One in particular, is HFWDurHoursNormal. This will allow you to enter the RecvdDate, RecvdTime, DateAcknow, TimeAcknow.

For instance:

HFWDurHoursNormal ({CALLLOG.RECVDDATE},{CALLLOG.RECVDTIME},{Asgnmnt.DATEACKNOW},{Asgnmnt.TIMEACKNOW})

will output "x Days x Hrs x Mins"

For hours other than 24x7, you select Customer Hours, or whatever you need...

stinsman

 

[NattyCat]

Have had a good look around Crystal and there are definately no HFW functions added - and trying to get people to authorise the download and install of extra functions in this company is like pulling teeth - I'm going to have to find out how to do this the hard way.

As far as the report is concerned, this is a one-off that I'm doing for a PA - have never used, seen or reported from HEAT before and I don't even have a connection to the database, am simply writing the report against saved data.

 

[DarthGAD]

If one of the fields is null because it has not been resolved then what are you calculating against? Current time?

Gus

 

[NattyCat]

I have a "Received" date-time field. And I have an "acknowledged" date-time field. There will always be a "received" date-time, but not always an "acknowledged" one.

I basically want to find out the difference between received and acknowledged. If there is no "acknowledged" date-time value, then the result can just be null.

so it would look something like:

Received Acknowledged Difference
2004/09/10 09:00:23 2004/09/10 09:15:11 0d 0h 15m
2004/09/10 11:45:15
2004/09/11 08:15:32 2004/09/10 11:15:32 0d 3h 0m

However, BECAUSE the acknowledged date-time field can contain nulls, I get the "bad date format string" error when Crystal evaluates the formula (as shown in earlier post) that I have used to convert the separate date and time strings into one date-time field.

 

[NattyCat]

OK, I fixed this report. For those who may be interested, this is how I did it.

I created a formula to check to see if the Date Acknowledged field contains nulls or "".

{@null ack}
if isnull({Asgnmnt.DATEACKNOW}) or {Asgnmnt.DATEACKNOW} = "" then "yes"

I then edited the {@Date Acknowledged} formula shown above to read:

{@Date Acknowledged}
if {@null ack} <> "yes" then
datetime(date({Asgnmnt.DATEACKNOW}) & " " & time({Asgnmnt.TIMEACKNOW}))

This works. It allows for null values.

Not sure why the previous null statement within the Date Acknowledged formula didnt work, but this one does.

Thanks to those who did try to help me.

Natasha

 

[synapsevampire]

Should have tried:

if not(isnull({Asgnmnt.DATEACKNOW}))
and
{Asgnmnt.DATEACKNOW} <> "" then
datetime(date({Asgnmnt.DATEACKNOW}) & " " & time({Asgnmnt.TIMEACKNOW}))

-k

 

[NattyCat]

Hi Vampire,

I did try that first, but it still gave me the error - which is the strangest thing. Have been pondering why this would be happening and I'm thinking it might be something to do with when the formula is evaluated - Perhaps the null or "" values need to be evaluated before the rest of the formula - which is why it worked with the "yes" formula and not when it was included in the date conversion. Would be interested to find out if this was the case.

 

[synapsevampire]

It's probably something simple like syntax.
-k