DateDiff in minutes

[ij76]

While I would have thought this was an easy one, it's just not working for me...

I have to find the difference in minutes between 2 datetime fields, here's an example of the info I have:

In a grouped field I have statuses with datetimes for each:

Open 7/12/2004 2:29:31PM
Assigned 7/13/2004 10:51:31AM
Responded 7/13/2004 1:31:43PM
Closed 8/17/2004 10:47:52AM

I need to first calculate the difference between the Open datetime and the first changed status in minutes. On another line I have to calculate the difference in minutes between the datetime from the Open status to the Closed status.

What I did was created a formula called Open_Time that says if the status is Open then datetime. I then created another for the first change saying datediff("n",Open_Time,datetime) and it's giving me 0.00.

I've created another formula called Closed_Time to display the datetime where the status is Closed. And another formula to get the difference between the Open status date and Closed status date by datediff("n", Open_time, Closed_time)...again, getting 0.00.

Any help would be much appreciated!

 

[synapsevampire]

Create 3 formulas:

In the group header reset them:
whileprintingrecords;
datetimevar Open:=cdate(1970,1,1);
datetimevar Assigned:=cdate(1970,1,1);
datetimevar Responded:=cdate(1970,1,1);
datetimevar Closed:=cdate(1970,1,1);

Details:
whileprintingrecords;
datetimevar Open;
datetimevar Assigned;
datetimevar Responded;
datetimevar Closed;
If {table.status} = "Open" then
Open:={table.datetime}
else
If {table.status} = "Assigned" then
Assigned:={table.datetime}
else
If {table.status} = "Responded" then
Responded:={table.datetime}
else
If {table.status} = "Closed" then
Closed:={table.datetime}

Now you can call these variables in the group footer, just check for the date being 1/1/1970, as in:

Group Footer:
whileprintingrecords;
datetimevar Open;
datetimevar Assigned;
datetimevar Responded;
datetimevar Closed;
if open <> cdate(1970,1,1) then
...

Should work for you.

-k

 

[ij76]

Woah, quite a bit more difficult than I thought. I've never been good at the variables....I appreciate the help, any chance you can explain a bit more in layman's terms? Basically, I don't see the 3 formulas you said I need to create and am not sure how to reset them in the group header. Also, the example I gave is just one example. The groups can have more than just those 4 statuses, and some may be repeated on different dates.

But I don't see how what you have suggested I do will give me a result (difference) in minutes.

 

[synapsevampire]

Each formula is designated by where it is to be placed in the supplied formulas, as in the "In the group header reset them:". That means that it goes in th Group Header.

"Details:" means that one goes in the details.

"Group Footer:" goes in the group footer.

Not sure how much clearer I can be.

Since you'd already posted that you understood the datediff function, I just supplied the means to do this, you would use the datediff against whatever corresponding statuses you need, if you have more than those statuses, then use them.

If you're just looking to get the difference in a linear fashion (meaning the previous rows datetime subtracted from the current rows datetime), you can just use a formula such as the following in the details:

datediff("n",{table.datetimefield},previous({table.datetimefield}))

That will show you the difference between each line in the details.

-k

 

[ij76]

Ok, I got all the variables down. Still have a problem though. I tried gettign the datediff from the two variables and am still getting a 0.00.

In the group footer I have this formula to find the difference in minutes:

datediff("n",datetimevar Open,datetimevar Resp)

What's wrong with it?

 

[lbass]

Try adding the following at the beginning of the formula:

WhilePrintingRecords;

-LB

 

[beanbrain]

When you say "first changed status" do you mean the difference between open and assigned? Wouldn't it be something like;

datediff(n, (table.open_time}, {table.assigned_time}) // difference between open and assigned.
datediff(n, {table.assigned_time}, {table.responded_time}) // difference between assigned and responded
datediff(n, {table.open_time}, {table.closed_time}) //etc.

The bit with datediff is you are comparing the difference between two dates where the later date is the second date.