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]

I figured this out by using the following formula. This will return number of days; hours and minutes.

CStr (int(Truncate (DATEDIFF('n',{@DateTimeRecd},{@DateTimeRtd}) /1440 ))) & "D " &

CStr (int(Remainder(Truncate (DATEDIFF('n',{@DateTimeRecd},{@DateTimeRtd}) /60 ),24))) & "H " &

CStr (int(Remainder(DATEDIFF('n',{@DateTimeRecd},{@DateTimeRtd}),60)))& "M "

I appreciate everyones assistance.

[Former Member]

Be very carefull with DateDiff. It's not dependable. DateAdd is better. I think that's one of the reasons that Luk showed it to you.

The best,

The Panda

[former_member434265]

I'm pretty sure I got the logic right on this one, it's been a while but excusing typos, this should do it in one line.

iif(totext(DateDiff ("n",{@DateTimeRecd},{@DateTimeRtd}) \ 60)>0, totext(DateDiff ("n",{@DateTimeRecd},{@DateTimeRtd}) \ 60) + " Hours and ","") + iif(DateDiff ("n",{@DateTimeRecd},{@DateTimeRtd}) mod 60 <= 0, "No Minutes", totext(DateDiff ("n",{@DateTimeRecd},{@DateTimeRtd}) - (DateDiff ("n",{@DateTimeRecd},{@DateTimeRtd}) \ 60)) + " minutes")

Thanks,

Zach

[Former Member]

I tried this and get a message stating a string is required. It then highlights the 0 directly after the > in the first statement.

iif(totext(DateDiff ("n",{@DateTimeRecd},{@DateTimeRtd}) \ 60)>0, totext(DateDiff ("n",{@DateTimeRecd},{@DateTimeRtd}) \ 60) + " Hours and ","") + iif(DateDiff ("n",{@DateTimeRecd},{@DateTimeRtd}) mod 60 <= 0, "No Minutes", totext(DateDiff ("n",{@DateTimeRecd},{@DateTimeRtd}) - (DateDiff ("n",{@DateTimeRecd},{@DateTimeRtd}) \ 60)) + " minutes")

[kyle_mcadam]

Get rid of the Totext() in the first statement, because you are comparing that against 0. The toText is only necessary in the two optional results.

[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})

[Former Member]

Morning Gary,

have you consider trying CTime? that might work. here is the example (Direct from the help file)

Returns

A Time value.

Action

CTime (number) converts the given number to a Time value; the given number is in units of 24 hours, can be fractional or negative as well.

CTime (string) converts and returns a time given a string that identifies a Time value.

CTime (dateTime) converts and returns a time given a DateTime value.

CTime (hh, mm, ss) converts and returns a time given the arguments of the hour, minute and seconds.

Examples

The following examples are applicable to both Basic and Crystal

syntax:

CTime (.2)

CTime (1.2)

Both return the same value 4:48:00 a.m.

CTime (-.2)

Returns 7:12:00 pm

CTime ("Jan. 6, 1999 2:30pm")

Returns 2:30:00 pm

CTime (#Feb. 24, 1999 2:40pm#)

Returns 2:40:00 pm

CTime (18, 30, 30)

Returns 6:30:30 pm

Comments

You can use the IsTime function to check if a String argument can be converted to a Time before doing the actual conversion. That way, if the conversion cannot be done, you can handle the situation appropriately.

Let me know how you get along with this.

Regards

Jehanzeb

[Former Member]

Hello and thank you for responding.

I have tried the CTime, but it only returns the time. I'm needing the difference between two DateTime fields. Broken down in HH:MM:SS.

Thanks again.