Days and hours between two dates??
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15879
Printed Date: 28 Apr 2025 at 12:40pm
Topic: Days and hours between two dates??
Posted By: jbalbo
Subject: Days and hours between two dates??
Date 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
|
Replies:
Posted By: DBlank
Date 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
|
Posted By: jbalbo
Date 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
|
Posted By: DBlank
Date 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
|
Posted By: jbalbo
Date 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
|
Posted By: DBlank
Date 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})
|
Posted By: jbalbo
Date 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?
|
Posted By: DBlank
Date Posted: 14 Mar 2012 at 11:20am
|
just insert a summary on the minutes formula at the group level
average(@formulafield, table.groupfield)
|
Posted By: jbalbo
Date 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
|
Posted By: DBlank
Date 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...
average( mailto:%7b@total_minutes - {@total_minutes } ,{RECORDED_SERVICE.Worker})
this should be placed in the group footer or group header
|
|