Print Page | Close Window

convert DateTime field to minute

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5627
Printed Date: 30 Apr 2024 at 8:36am


Topic: convert DateTime field to minute
Posted By: rp123
Subject: convert DateTime field to minute
Date Posted: 26 Feb 2009 at 11:53pm
Hey Guys,

I need help with DateTime field in crystal report XI.

Q1. I need to convert the {probsummarym1.outage.dur} DateTime field to minute.

For example it display 2:48 AM as time and I need to convert it to minute.

Q2. I want to sum the {probsummarym1.outage.dur} and {probsummarym1.delayed.dur}. These are DateTime fields and for example display 2:48 AM as data in report.Then I need to create the chart for the total minute.

I am very new to Crystal Report so can you write step by step process?

Thanks

-------------
rp


Replies:
Posted By: rahulwalawalkar
Date Posted: 27 Feb 2009 at 1:01am

Hi

Please can you post some sample records for both datetime fields,Also what type of chart needs to be generated and the data to be displayed on X-axis and Y -axis.
 
Cheers
Rahul

Posted By: rp123
Date Posted: 27 Feb 2009 at 1:12am
Sample record for {probsummarym1.outage.dur}

02:48:00AM
12:39:00 AM
09:43:00AM

Sample record for {probsummarym1.delayed.dur}

01:17:00PM
1:39:00 AM
08:40:00PM

I have to create single bar chart which display total sum for both fields ({probsummarym1.delayed.dur} & {probsummarym1.outage.dur})

Another question.

Q.When I export the report to excel it mess up all the format/settings. How can I get the correct excel format.




-------------
rp

Posted By: rahulwalawalkar
Date Posted: 27 Feb 2009 at 2:34am

Hi

This is what you do

Create the below formulas

Frm_outage_Mins

Minute({probsummarym1.outage.dur})

Frm_delayed_Mins
Minute({probsummarym1.delayed.dur})

 
Then Insert Summary,in that Field to summarize select the Frm_outage_Mins type of summary sum and insert in report footer this will give you sum for outage minutes.
 

Repeat the same steps for delayed minutes

 
This will give you total minutes for outage and delayed, then create a final formula to add the output of the above formulas.
 

Frm_Total_Mins
mailto:Frm_delayed_Mins%7d+%7b@Frm_Outage_Mins - {@Frm_delayed_Mins}+ mailto:%7b@Frm_Outage_Mins - {@Frm_Outage_Mins }

this will give you total minutes.

 
Then Insert Chart in report header ,then in data tab select for all records.

Show Values
Move the Frm_Total_Mins formula 

 
Click Ok
 
Ans2) for exporting to excel depends what expoting options you are using you will need to read guidelines in crystal help......
 
Cheers
Rahul

Posted By: rp123
Date Posted: 27 Feb 2009 at 3:01am
Thanks for quick reply.

I ran the formula but it just shows the minute only.

Take a example: I have 2 hours and 48 minute as data but when the formula is run then it just shows the minute part only i.e. 48 minute.

I want to convert the 2 hours also so the total minute for 2 hours 48 minute will be 168 minute.



-------------
rp

Posted By: rahulwalawalkar
Date Posted: 27 Feb 2009 at 4:46am
Hi
 
Sorry for the previous formulas here what you do
 
Convert Hours to Minutes
 
Frm_HrstoMinutes
Local TimeVar TT;
local numbervar hours;
TT := Time({probsummary.outagedur});
// Parse out the hours portion of the string and
// multiply by 3600 to convert to seconds
hours := tonumber(totext(tt)[1 to 2])* 3600;
hours/60
Convert Minutes to Number
 
MinutestoNumber
Local TimeVar TT;
local numbervar minutes;
TT := Time({probsummary.outagedur});
// Parse out the minutes portion of the string and
// multiply by 60 to convert to seconds
minutes := tonumber(totext(tt)[4 to 5]) * 60;
// Add up all the seconds
minutes/60
 
Then add final formula
 
Total Minutes
mailto:%7b@Frm_HrstoMinutes%7d+%7b@Minutestonumber - {@Frm_HrstoMinutes}+{@MinutestoNumber }
 
Then Insert summary Sum Total Minutes in Report Footer
 
For Chart Show Values select formula Total Minutes.
 
Cheers
Rahul
 

Posted By: rp123
Date Posted: 27 Feb 2009 at 6:52pm
Hi,

I have one problem coming. Take a example:

I have 00:39 AM as the data so when the formula is run it is also converting the 00 to minute. The actual minute used in this case should be only 39 minutes not 759 minutes. Can you help?


-------------
rp

Posted By: rp123
Date Posted: 27 Feb 2009 at 8:02pm
and how  to convert a data which is:


1 18:37 AM to minutes where 1 is one day and 18 is hours and 37 is minutes.

The answer is 2557 minutes.


-------------
rp


Print Page | Close Window