How to separate dates store in the same field
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22225
Printed Date: 29 Apr 2024 at 10:27am
Topic: How to separate dates store in the same field
Posted By: Ytalo
Subject: How to separate dates store in the same field
Date Posted: 07 Feb 2017 at 3:53am
I am trying to extract two different dates data store in the same field, depending on a different field...
log.date lot.type
01/01/2016 open
01/03/2016 closed
What I am trying to do is to put the open date in one column and closed date in a different column, so I can get the duration of time between open date and closed date... thanks for any help.
|
Replies:
Posted By: kevlray
Date Posted: 07 Feb 2017 at 4:32am
There are lots way this is possible. But one way that I like to do it is with shared variables. It works better if you are grouping by something. So in the details you would have a formula. One for the open date and one for the closed date (You could eliminate part of the code if you can guarantee the order of the dates).
The code for each formula would be similar
shared vardate opendate;
if {lot.type} = 'open' then opendate := {log.date};
// This assumes more than two types
shared vardate closeddate;
if (lot.type} = 'closed' then closeddate := {log.date};
then in the group footer you would have a formula for each variable .
shared opendate; //This will display the date
Then you will have a formula that will do a date diff (I do not know what kind of duration you want).
|
Posted By: Ytalo
Date Posted: 07 Feb 2017 at 4:50am
The date field has a time too, and I am trying to get the duration in HH:MM:SS, from open to close
Thank you for your help, I am going to try it now.
|
Posted By: Ytalo
Date Posted: 07 Feb 2017 at 5:06am
|
It didn't work... I am not proficient in writing formulas can you explain in detail... and again thank you for your help and patience.
|
Posted By: kevlray
Date Posted: 07 Feb 2017 at 7:00am
Opps!! I wrote the variable declarations wrong. They should be datevar not vardate.
First you did not explain if you were grouping. It works pretty well if you are grouping.
The code above (with the if statements) would go in the details section. Then in the group footer you would have a formula similar to below.
shared datevar opendate;
shared datevar closeddate;
diff=datediff( "s",opendate, closeddate);
// convert to string as 00:00:00 will find code later
|
Posted By: Ytalo
Date Posted: 07 Feb 2017 at 7:22am
I get an error message after diff=datediff......
"the remaining text does not appear to be part of the formula"
|
|