IO_Dork

asked on

# crystal reports datediff formula returning "12" when less than hour difference.

datediff function returning "12:01" hours and min when its just one minute. Once the time diff exceeds 1 hour it shows properly - "2:05". the "12" should just be showing "00". Why is it not showing properly?

formula:

Time ((DateDiff ('n', {proposalhistory.last_timestamp},{proposalhistory.timestamp}) / 60) / 24)

Agree. The other way is to calculate hours and minutes separately and combine hem yourself as a string.

If you need to do other calculations on the durations, you would have to convert then do the calculation

If you need to do other calculations on the durations, you would have to convert then do the calculation

ASKER

i hear you and that's what I interpreted it to be, but the results is a time interval not actual clock time. So, if there is a time interval calculated to be just 4 mins. It should not be showing as 12:04:00am, it should just show as 00:04:00. I guess I need to do what Mike suggested otherwise it will always try to show it as actual clock time with am and pm with 12 showing in the hours section if the time interval is less than 1hr.

ASKER

i think I am just using the wrong formula seeing that I am using the Time function.

ASKER

So I also tried: TimeValue ({proposalhistory.timestamp} - {proposalhistory.last_timestamp})

but that still gives me 12 as the hour when the time diff is less than an hour, all the other times over an hour show correctly.

So I am not sure how to write a combined function that displays correctly without the "12" hr for time diff < 1hr:

00:00:05 when time diff is < 60sec

00:05:00 when time diff is = 60sec and < 1hr

05:00:00 when time diff is => 1hr

Time diff could be greater than 24hr, but its fine if my hours go beyond 24 and show 36, 48, etc

but that still gives me 12 as the hour when the time diff is less than an hour, all the other times over an hour show correctly.

So I am not sure how to write a combined function that displays correctly without the "12" hr for time diff < 1hr:

00:00:05 when time diff is < 60sec

00:05:00 when time diff is = 60sec and < 1hr

05:00:00 when time diff is => 1hr

Time diff could be greater than 24hr, but its fine if my hours go beyond 24 and show 36, 48, etc

TimeValue is just another name for the Time function, so it's going to give you the same results.

You're looking for a duration, but Time returns a time of day. From the CR Help:

As I said before, you could format the field to show the time in a 24 hour format, instead of AM/PM, and then you'd get 0:00 instead of 12:00. But because Time is producing a time of day, it won't give you any values above 23:59:59. For example, if you give it a value that's 25 hours, you just get 1:00. To see a duration that's 24 hours or more, you need to use something else. Rather than create a formula myself, I'll just point you to Ken Hamady's site. He's got a couple of formulas that should work for you:

https://www.kenhamady.com/formulas/form09.shtml

You could replace the {YourTable.TotalSeconds} in his formulas with your DateDiff function, but change the interval that you use in DateDiff from 'n' (minutes) to 's' (seconds). Or, if you want to keep using the difference in minutes, you could decrease his divisors by a factor of 60 (because you're dividing minutes, not seconds).

James

PS: As a side note, you might want to look at the other formulas on Ken's site. He's got a lot of useful things there.

You're looking for a duration, but Time returns a time of day. From the CR Help:

Returns a Time value that represents the time, given a String expression specifying a time from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.), inclusive.

As I said before, you could format the field to show the time in a 24 hour format, instead of AM/PM, and then you'd get 0:00 instead of 12:00. But because Time is producing a time of day, it won't give you any values above 23:59:59. For example, if you give it a value that's 25 hours, you just get 1:00. To see a duration that's 24 hours or more, you need to use something else. Rather than create a formula myself, I'll just point you to Ken Hamady's site. He's got a couple of formulas that should work for you:

https://www.kenhamady.com/formulas/form09.shtml

You could replace the {YourTable.TotalSeconds} in his formulas with your DateDiff function, but change the interval that you use in DateDiff from 'n' (minutes) to 's' (seconds). Or, if you want to keep using the difference in minutes, you could decrease his divisors by a factor of 60 (because you're dividing minutes, not seconds).

James

PS: As a side note, you might want to look at the other formulas on Ken's site. He's got a lot of useful things there.

ASKER

Ok I will look. Thanks

Try this

```
Local numbervar minutes;
Local numbervar hours;
minutes := DateDiff('n',{proposalhistory.last_timestamp}, {proposalhistory.timestamp);
hours := minutes \ 60;
minutes := minutes mod 60;
CStr(Hours,0) & ':' & CStr(minutes, '00')
```

ASKER

Thanks Mike. this works great, but it does not allow me to do a group average of this formula. I assume b/c it results in a string? is there a way to convert it to a number so I can apply averages to it?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

James