| Author |
Message |
jbalbo
Senior Member
Joined: 17 Feb 2011
Online Status: Offline
Posts: 219
|
   Topic: Days and hours between two dates??
Posted: 14 Mar 2012 at 6:43am |
Hi,
I'm trying to use datedif, but I want to display days and hours between two date\time fields
example:
date1 1/1/12 09:00
date2 1/1/12 11:00
formula would display 0 days 2hrs
date1 1/1/12 09:00
date2 1/2/12 11:00
formula would display 1 day 2 hrs
Thanks
Joe
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Mar 2012 at 7:28am |
are date 1 and date 2 on the same row or different rows?
this will impact how to use the datediff().
basically though you can use datediff with hours and then use that result to make a string to display your desired output.
something like
local numbervar tt := datediff('h',date1,date2);
local numbervar d := truncate(tt/24,0);
local numbervar h := remainder(tt,24);
local stringvar tt_d := totext(d,0,"") + ' days ';
local stringvar tt_h := totext(h,0,"") + ' hours';
local stringvar tt_string := tt_d & tt_h
Edited by DBlank - 14 Mar 2012 at 7:29am
|
|
IP Logged |
|
jbalbo
Senior Member
Joined: 17 Feb 2011
Online Status: Offline
Posts: 219
|
Posted: 14 Mar 2012 at 7:47am |
Thanks for the info
the date is on the same line.
Not sure how to use the info you provided?
Thanks Again
Joe
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Mar 2012 at 8:07am |
use this formula in a new formual field but repalce date1 and date2 with your actual database fields
local numbervar tt := datediff('h',date1,date2);
local numbervar d := truncate(tt/24,0);
local numbervar h := remainder(tt,24);
local stringvar tt_d := totext(d,0,"") + ' days ';
local stringvar tt_h := totext(h,0,"") + ' hours';
local stringvar tt_string := tt_d & tt_h
|
|
IP Logged |
|
jbalbo
Senior Member
Joined: 17 Feb 2011
Online Status: Offline
Posts: 219
|
Posted: 14 Mar 2012 at 9:19am |
Thanks so much,
Works great I added some logic for minutes
I think is correct?
local numbervar tt := datediff('h',{RECORDED_SERVICE.STARTTIME},{RECORDED_SERVICE.INSERT_DATE});
local numbervar d := truncate(tt/24,0);
local numbervar h := remainder(tt,24);
local numbervar m := remainder(tt/24,1440);
local stringvar tt_d := totext(d,0,"") + ' days ';
local stringvar tt_h := totext(h,0,"") + ' hours';
local stringvar tt_m := totext(m,0,"") + ' minutes';
local stringvar tt_string := tt_d & tt_h & tt_m
Sorry I have one more ??
What if I wanted to do summaries on grouping?
Like shortest time, Average time etc...
Thanks
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Mar 2012 at 10:07am |
you will have to change it at the base if you want to include minutes because your datediff() is currently only giving you hours so your minutes would always be 0.
here is one way I have done it in the past but ther emay be a more elegant solution....
local numbervar tt := datediff('n',{table.DATE1},{table.DATE2});
local numbervar d := truncate(tt/1440,0);
local numbervar h := truncate(remainder(tt,1440)/60,0);
local numbervar m := truncate(remainder(tt,60),0);
local stringvar tt_d := if d=0 then "" else totext(d,0,"") + 'd ';
local stringvar tt_h := if h = 0 then "" else (totext(h,0,"")) + 'h ';
local stringvar tt_m := totext(m,0,"") + 'm ';
local stringvar tt_string := tt_d & tt_h & tt_m
for any calculations you will have to create a formula to return the minutes only. You cannot get your desired math calculations from a string.
datediff('n',{table.DATE1},{table.DATE2})
Edited by DBlank - 14 Mar 2012 at 10:12am
|
|
IP Logged |
|
jbalbo
Senior Member
Joined: 17 Feb 2011
Online Status: Offline
Posts: 219
|
Posted: 14 Mar 2012 at 11:07am |
This works great ..
Thanks
Im going to work on trying to do averages for the above on group footers?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Mar 2012 at 11:20am |
just insert a summary on the minutes formula at the group level
average(@formulafield, table.groupfield)
Edited by DBlank - 14 Mar 2012 at 11:20am
|
|
IP Logged |
|
jbalbo
Senior Member
Joined: 17 Feb 2011
Online Status: Offline
Posts: 219
|
Posted: 15 Mar 2012 at 8:47am |
Sorry,
Im just not getting it..
if my formula is named averagetime
and the formula contains what you gave me
local numbervar tt := datediff('n',{table.DATE1},{table.DATE2});
local numbervar d := truncate(tt/1440,0);
local numbervar h := truncate(remainder(tt,1440)/60,0);
local numbervar m := truncate(remainder(tt,60),0);
local stringvar tt_d := if d=0 then "" else totext(d,0,"") + 'd ';
local stringvar tt_h := if h = 0 then "" else (totext(h,0,"")) + 'h ';
local stringvar tt_m := totext(m,0,"") + 'm ';
local stringvar tt_string := tt_d & tt_h & tt_m
I use
average(@averagetime, table.groupfield)- this the group name?
Thanks
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Mar 2012 at 8:56am |
nope.
you need one fomrula to only get the minutes bewteen date 1 and date 2
//total_minutes
datediff('n',{table.DATE1},{table.DATE2})
now if you want the average you can create a new formula as
average(@total_minutes)
this should be palced in the Report header or footer
now if youwant the average at a group level you have to use the group condition in the formula which is the the name of the field you are grouping on.
average(@total_minutes , groupfield_goes_here)
somthing like this...
this should be placed in the group footer or group header
|
|
IP Logged |
|
|
|
