How do I return the difference between two DateTime fields as hours and min

[Former Member]

I'm trying to return the difference in hours and minutes between two DateTime fields.

I'm currently using the following

DateDiff ("n",{@DateTimeRecd},{@DateTimeRtd})

However, this only returns the number of mintues. Of course I can change it to DateDiff ("h",{@DateTimeRecd},{@DateTimeRtd}), but this is only hours.

I'm needing the time in hours and minutes.

Any ideas?

Thank you in advance for your assistance.

[Former Member]

Try this:

Cstr(Dateadd(-Second(),),"HH:mm")

[Former Member]

I tried your recommendation, Cstr(Dateadd(-Second(),),"HH:mm") and was unable to get it to work.

Mine looked like this Cstr(Dateadd(-Second({@DateTimeRecd}),{@DateTimeRtd}),"HH:mm") and I kept getting a message stating "Not enough arguments have been given to this function."

I'm probably doing this wrong as I'm new to Crystal.

Any other ideas?

Thanks again.

[Former Member]

I might be wrong but maybe you are missing ,"pmStr" for pm or "amStr" for am at the end of the argument?

here is the example which I have seen under crystal help file

CStr(CTime(12, 10, 10), "HH*mm*ss tt", "amStr", "pmStr")

Returns "121010 pmStr".

obviously ignore the middle part as you replaced it with yours however, after "hh:mm" it says "amStr","pmStr" which is missing in your coding.

try it might work.

Regards

Jehanzeb

[Former Member]

This looks pretty good. I had to play with it a little.

I started with putting the datediff in a variable:

shared numbervar i :=

datediff("n", #07/17/2008#,CurrentDateTime - .1)

Then I passed this to another formula:

shared numbervar i;

Cstr(DateAdd ("n", i, CurrentDateTime), "HH:mm")

and I got: 16:28

The "DateAdd" + "Cstr" idea should work for you.

The Panda

[Former Member]

I never thought it would be so difficult to convert minutes to hours and minutes. lol....

Ok, I tried both ideas and was unable to get either to work.

I was able to write this formula which returns the difference between my 'received' time and my 'returned' time in minutes.

DateDiff ("n",{@DateTimeRecd},{@DateTimeRtd})

My hope is for a simple solution to convert these 'minutes' into 'hours' and 'mintues.'

Any other ideas?

Thanks again in advance.

[Former Member]

Hey Gary,

I think everybody wants to make this more complicated than it really is.

give this a try:

TOTEXT(DATEDIFF('h',{@BeginDateTime},{@EndDateTime}),"0")+"H "+
TOTEXT(
DATEDIFF('n',{@BeginDateTime},{@EndDateTime}) - 
(DATEDIFF('h',{@BeginDateTime},{@EndDateTime})*60)
,"0")+"M "+
TOTEXT(
DATEDIFF('s',{@BeginDateTime},{@EndDateTime}) - 
(DATEDIFF('n',{@BeginDateTime},{@EndDateTime})*60)
,"0")+"S"

Returns a format like this: 0H 0M 0S

Hope this helps,

Jason

[Former Member]

This is great! I created this formula and it is returning the hours, minutes and seconds. However, for some reason some of the hours, minutes or seconds are showing as a negative number.

Examples directly from the report:

Received Time______________Returned Time____________Time Difference

7/14/08 10:26:00 AM_________7/14/08 2:12:20 PM_______4H -14M 20S

7/16/08 9:49:00 AM__________7/16/08 10:39:04 AM______1H -10M 4S

Any ideas on how to correct this?

Thanks again!

Edited by: Gary Timm on Jul 21, 2008 5:19 AM

[Former Member]

Use the "Abs" function after the call to "DateDiff" to get rid of the negative number

[Former Member]

Gary,

This is my bad. Apparently the DateDiff function has some properties that I was unaware of... Namely it will look for the the the time unit designated and ignore the remainder of the time

For example:

DATEDIFF('h',#7/14/08 10:26:00 AM#,#7/14/08 2:12:20 PM#) will return 4, when in fact it should only be 3.

So here is my solution... Create a formula called Seconds where:

Seconds = DATEDIFF('s',{@BeginDateTime},{@EndDateTime})

Then create the following custom function, named "DTMSsecs":

Function DTMSsecs (Seconds As Number)
    DTMSsecs = _
Left(totext(int(Seconds / 86400)),len(totext(int(Seconds / 86400)))-3) _
&"d "& _
Left(totext(int((((Seconds))- ((int(Seconds / 86400)) * 86400)) /3600)),len(totext(int((((Seconds))- ((int(Seconds / 86400)) * 86400)) /3600)))-3) _
&"h "& _
Left(totext(int((((Seconds))-((int(Seconds / 86400)*86400)+ _
(int((((Seconds / 86400)*86400)-((int(Seconds / 86400))*86400))/3600)*3600)))/60)), _
      Len(totext(int((((Seconds))-((int(Seconds / 86400)*86400)+ _
      (int((((Seconds / 86400)*86400)-((int(Seconds / 86400))*86400))/3600)*3600)))/60)))-3) _
&"m "& _
Left(totext(int(((Seconds))-((int(Seconds / 86400)*86400)+(int((((Seconds / 86400)*86400)- _
((int(Seconds / 86400))*86400))/3600)*3600)+(int((((Seconds / 86400)*86400)- _
((int(Seconds / 86400)*86400)+(int((((Seconds / 86400)*86400)-((int(Seconds / 86400))*86400))/3600)*3600)))/60)*60)))), _
      Len(totext(int(((Seconds))-((int(Seconds / 86400)*86400)+(int((((Seconds / 86400)*86400)- _
((int(Seconds / 86400))*86400))/3600)*3600)+(int((((Seconds / 86400)*86400)- _
((int(Seconds / 86400)*86400)+(int((((Seconds / 86400)*86400)-((int(Seconds / 86400))*86400))/3600)*3600)))/60)*60)))))-3) _
&"s"
End Function

This is an old function that I did not long after I started using CR, so it's not the cleanest formula in the world but it does work. Also, it has days included in the function... If you don't want the days included you can scrub it from the function's formula.

The final formula should look like this:

Time = DTMSsecs({@Seconds})