Convert hour/minutes to units(days)
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=6193
Printed Date: 23 May 2024 at 12:14am
Topic: Convert hour/minutes to units(days)
Posted By: crystal_newbiee
Subject: Convert hour/minutes to units(days)
Date Posted: 27 Apr 2009 at 6:42am
Hello everyone,
I was just wondering if anybody could help me. I have a crystal report which currently reads the information in through a text file. The text file has 2 separate number fields, hour and minute which are currently displayed as hour and minute.
I need to display the fields as units, which works out as (4 units per day), so as 1/4 day = 1.75 hrs: allowing for rounding, upto 2hours = 1 unit, upto 3 1/2hours = 2 units, 5hours = 3 units, 7 hours = 4units. Thus meaning that 10days would equate to 40units. I'm hoping to do this in 2 seperate formula fields on the report. Any help would be great.
Thanks very much.
file:///C:%5CDOCUME%7E1%5Ckevh%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - -
-------------
With power comes great responsibility!
|
Replies:
Posted By: DBlank
Date Posted: 27 Apr 2009 at 6:48am
|
can you post some sample data?
|
Posted By: crystal_newbiee
Date Posted: 27 Apr 2009 at 6:56am
yeah sure. Will just get something. Thanks
-------------
With power comes great responsibility!
|
Posted By: crystal_newbiee
Date Posted: 27 Apr 2009 at 7:05am
"JobSheet","Status","AllocatedTo","AllocatedDate","EnteredBy","EnteredDate","RequiredBy","JobDescription","DevelopmentArea","Reportedby","ReportTitle","ReportSubTitle","Priority","CallType","Department","sort","EstimatedHours","EstimatedMinutes","Type","IncidentClass","Completed","Allocation Notes","Completion Notes","Rejection Notes","Days"
"00010334","Entered","","","KEVH","27/04/2009","","test 10","Unallocated","KEVH","job request by priority","",5,"Unallocated","IT","5","2","25","Unallocated","I","","","","",0
"00010333","Entered","","","KEVH","27/04/2009","","test 8","Unallocated","KEVH","job request by priority","",5,"Unallocated","IT","5","10","0","Unallocated","I","","","","",0
"00010332","Entered","","","KEVH","27/04/2009","","test 6","Unallocated","KEVH","job request by priority","",5,"Unallocated","IT","5","16","35","Unallocated","I","","","","",0
"00010331","Entered","","","KEVH","27/04/2009","","test 5","Unallocated","KEVH","job request by priority","",5,"Unallocated","IT","5","0","45","Unallocated","I","","","","",0
"00010330","Entered","","","KEVH","27/04/2009","","test","Unallocated","KEVH","job request by priority","",5,"Unallocated","IT","5","2","0","Unallocated","I","","","","",0
"00010329","Entered","","","KEVH","27/04/2009","","test","Unallocated","KEVH","job request by priority","",5,"Unallocated","IT","5","3","30","Unallocated","I","","","","",0
"00010328","Entered","","","KEVH","27/04/2009","","test","Unallocated","KEVH","job request by priority","",5,"Unallocated","IT","5","6","45","Unallocated","I","","","","",0
"00010327","Complete","","","KEVH","27/04/2009","","this is a test","Unallocated","KEVH","job request by priority","",5,"Unallocated","IT","5","1","1","Unallocated","I","","","","",0
-------------
With power comes great responsibility!
|
Posted By: crystal_newbiee
Date Posted: 27 Apr 2009 at 7:05am
Is this ok? Could not find anywhere to attach a txt file. Thanks
-------------
With power comes great responsibility!
|
Posted By: crystal_newbiee
Date Posted: 27 Apr 2009 at 7:07am
I guess it could all be done in one formula if possible? Or if it makes it simpler, thanks again.
-------------
With power comes great responsibility!
|
Posted By: lockwelle
Date Posted: 27 Apr 2009 at 7:09am
|
getting the hours, is easy, as I am sure you know. It is the conversion as there appears to be a range of time that counts for each unit, not an exact amount which is going to make the conversion iffy, at least that is my initial impression.
|
Posted By: crystal_newbiee
Date Posted: 27 Apr 2009 at 7:13am
Could it be rounded to the nearest hour maybe? i,e 3hours 25 mins would equal 2 units? If that makes sense? thanks
-------------
With power comes great responsibility!
|
Posted By: lockwelle
Date Posted: 27 Apr 2009 at 7:32am
|
you can create the hours by:
val({estimated hours}) + val({estimated minutes})/60
if you wanted to round, you could round on this result, or if you wanted to compare this, I would place the result into a local numbervar and reference it in formula.
This assumes that the fields are treated as strings and not numbers...it they are number, just drop the val() part.
|
Posted By: crystal_newbiee
Date Posted: 27 Apr 2009 at 8:03am
The fields are treated as numbers, so would my formula for the field be: {OutstandingJobSheets_txt.EstimatedHours} + {OutstandingJobSheets_txt.EstimatedMinutes}/60. I am quite new to this so I do apologise. Also how do you actually round on the result? Thanks again.
-------------
With power comes great responsibility!
|
Posted By: crystal_newbiee
Date Posted: 27 Apr 2009 at 8:18am
If I drop the val() partof the formula, I get a message saying "A number, or currency amount is required here". Sorry to be a pain. Thanks for your quick response.
-------------
With power comes great responsibility!
|
Posted By: crystal_newbiee
Date Posted: 27 Apr 2009 at 8:29am
Sorry I think you was correct the first time. They are passed in as strings. But the formula is just adding up what is in both fields?
-------------
With power comes great responsibility!
|
Posted By: DBlank
Date Posted: 27 Apr 2009 at 9:07am
|
val({estimated hours}) + val({estimated minutes})/60
will add the hours to the minutes after they are divided by 60 (turing it into an hour format) and round to the nearest number with no decimals.
E.g.
2 hours + 40 minutes/60 = 3
2 hours + 29 minutes/60=2
|
Posted By: lockwelle
Date Posted: 28 Apr 2009 at 6:20am
|
I wouldn't think that this would round, unless you set the rounding value for the formula...which is probably the easiest way to go about it.
I am just wondering how the estimated times relates to actual...it they estimate 2 hours, and have 4 of those in 1 day, then the day is 8 hours, but they could estimate 1 procedure at 7 hours and that would be a day...so it just seems a bit confusing...but it might make perfect sense in the company.
|
Posted By: DBlank
Date Posted: 28 Apr 2009 at 6:56am
|
Lockwelle, I thought this would not round either but in a basic testing of it it did. Not sure if my "test" was legitimate but surprisingly it was the end result.
|
Posted By: lockwelle
Date Posted: 28 Apr 2009 at 7:24am
|
Well, that is something to watch for...not sure that I would always want it to round.
|
Posted By: crystal_newbiee
Date Posted: 29 Apr 2009 at 1:51am
Hi, thanks for your responses. the formula I am using is:
val({EstimatedHours}) + val({EstimatedMinutes}) / 60. The /60 part does not seem to do anything though, is this because its a string? This does work for the values you sent, but if there was say 70hours, then 70units are displayed? Where as it should be 40units for 70hours.
Sorry to be a pain.
This is the way they want it displaying, hope you can help and thank you again.
-------------
With power comes great responsibility!
|
Posted By: lockwelle
Date Posted: 29 Apr 2009 at 6:14am
|
this just gives the hours, not the units. for the units you need to perform a conversion. Are these times for the details, and you will sum the details to create a total of units for a week or other time period? If so, you might want to do something like:
local numbervar thisTime:=val({EstimatedHours}) + val({EstimatedMinutes}) / 60;
local numbervar thisUnit:=0;
shared numbervar totalUnits; //(optional, if aggregate needed)
if thisTime>=1.75 and thisTime <2 then
thisUnit :=1
else
if thisTime >= ... and thisTime < ... then
thisUnit:=2...
I think you get the idea.
totalUnits := totalUnits + thisUnit; //(optional, for the aggregate)
thisUnit //display the units calculated
if you use the aggregates, you will need a formula to display them and one to reset to 0 when the group changes.
|
Posted By: DBlank
Date Posted: 29 Apr 2009 at 6:28am
|
Could you change this to total minutes / 105 (one unit)?
(val({EstimatedHours})*60 + val({EstimatedMinutes})) / 105
|
Posted By: crystal_newbiee
Date Posted: 29 Apr 2009 at 8:05am
Hi,
Yes it can be changed to total minutes, is this an easier way?
Ok so I created the NumberVar within the same formula, wont I need to create a quite a big IF statement by doing it this way though? The total will be needed. The Units are purely for report use.
Could I do it like:
1 unit = 105mins
2 units = 210mins
3 units = 315mins
4 units = 420mins......etc.
I will know the figures are right when the value for say 70hours(10 days) equals to 40 units. For this I did 70(hours) * 60(minutes) = 4200(minutes) / 105(unit) = 40units.
I hope this makes sense and really appreciate your help guys.
Thanks.
@DBlank - yes this formula worked:
(val({OutstandingJobSheets_txt.EstimatedHours})*60 + val({OutstandingJobSheets_txt.EstimatedMinutes})) / 105
Thank you both very much. Think I am going to have to learn crystal reports alot more as it's required quite alot in my job. It looks to be outputting what I need but will reply again.
Cheers.
-------------
With power comes great responsibility!
|
Posted By: lockwelle
Date Posted: 29 Apr 2009 at 8:14am
|
if you're units are based on 105 minutes, you can simplify the calculation of units.
local numbervar thisUnits;
thisUnits := ROUND(totalMinutes / 105)
all depends, if the estimate is for 130 minutes, how many units is it? Or can this not happen?
If 105 minutes is the 'unit' all you need to do is divide, so the if statement is simple...it is if there are any 'extra' minutes to account for, and how to do that.
|
Posted By: crystal_newbiee
Date Posted: 01 May 2009 at 6:56am
Hi,
yes the units are based on 105minutes. The formula is working at present for character values. But I have another report which has numeric values, so I changed the formula to:
{OutstandingJobSheets_txt.EstimatedHours}*60 + {OutstandingJobSheets_txt.EstimatedMinutes} / 105
But this give strange results. For example, 6hours 30minutes is displayng as 360? Also, there are occasions when 0units are displayed on the initial report. Like for 0 hours 45 minutes no units are displayed when 1 unit should be displayed.
The estimate for 130 minutes would display 2 units, so there is an easier way to do this would you say? Thanks again, response time from you both has been excellent.
Kev
-------------
With power comes great responsibility!
|
Posted By: lockwelle
Date Posted: 01 May 2009 at 7:00am
|
you need ()...it is taking hour/60 then adding minutes/105
you want (hour/60+minutes)/105
|
Posted By: DBlank
Date Posted: 01 May 2009 at 7:00am
|
You need to parenth the first part of the statement so that it does the addition of the two then divide that by 105. Your statement is is dividing the minutes only:
({OutstandingJobSheets_txt.EstimatedHours}*60 + {OutstandingJobSheets_txt.EstimatedMinutes}) / 105
|
Posted By: crystal_newbiee
Date Posted: 01 May 2009 at 8:16am
Thanks, and how can I make it so that the units are always greater than 0? Say if the job is only 10 or 20minutes? can I add this to the same formula as the unit should never be below 1. thanks again.
Kev
-------------
With power comes great responsibility!
|
Posted By: DBlank
Date Posted: 01 May 2009 at 8:18am
|
if
({OutstandingJobSheets_txt.EstimatedHours}*60 + {OutstandingJobSheets_txt.EstimatedMinutes}) / 105 <1 then 1
else
({OutstandingJobSheets_txt.EstimatedHours}*60 + {OutstandingJobSheets_txt.EstimatedMinutes}) / 105
|
|