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

 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

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

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.

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

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

 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.

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

Select allOpen in new window

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?