asked on

# crystal reports datediff formula returning &quot;12&quot; 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)

Crystal Reports

Last Comment
James0628

8/22/2022 - Mon
James0628

The time starts at midnight, which is 12:00 AM.  The simple fix would be to change the format for the field to use "military time" (ie. a 24 hour clock, so midnight is 0, 1 PM is 13, etc.).

James
Mike McCracken

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
IO_Dork

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.
IO_Dork

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

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
James0628

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:

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.
IO_Dork

ASKER
Ok I will look. Thanks
Mike McCracken

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')
``````
IO_Dork

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
James0628

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.