Avatar of IO_Dork
Flag for United States of America 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?


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

Crystal Reports

Avatar of undefined
Last Comment

8/22/2022 - Mon

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

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

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

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:


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


 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.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

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

Open 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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

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.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.