I need a field to enter minutes that calculates every 60 minutes = 1 hour, etc.

I have a data entry form that users need to enter in their Approx. Time Spent (in minutes).  For example, they work on an item for 10 minutes, I want them to just have to enter in "10" but if they worked on something for an hour and a half, I want them to enter "90".  The data entered into this form I will be showing on a grid and I want the grid to compute that the total for those 2 items, "10" minutes and "90" minutes, was a total of 2 Hours and 40 minutes.

Any help is appreciated!
Norm-alNetwork EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ThommyCommented:
Are you looking for a function to convert minutes to hours/minutes???

Please try my code...
function MinutesToHours(Minutes: Integer): string;
var
  Hours: Integer;
  Minutes: Integer;
begin
  if (Minutes > 0) then begin
    Hours   := Minutes div 60;
    Minutes := Minutes mod 60;
  end
  else begin
    Hours   := 0;
    Minutes := 0;
  end;

  Result := Format('%.2d:%.2d', [Hours, Minutes]);
end;

Open in new window

0
Norm-alNetwork EngineerAuthor Commented:
Hmm... That might work. I can make my data entry field a regular Text field that users enter in minutes, then the grid can calculate the field.... I will try that now.
0
Ephraim WangoyaCommented:
There are two ways you can do it, either loop the dataset or get it straight from the database

Here are the codes
Method 1 - Get from database

procedure TForm1.Button1Click(Sender: TObject);
//Edit1.Text has the user name
var
  Q: TADOQuery;
  Total: Integer;
begin
  Q := TADOQuery.Create(nil);
  try
    Q.Connection := ADOConnection1;
    Q.SQL.Add(Format('SELECT SUM([Time Spent]) CNT FROM TABLE1 WHERE USER = %s', [Edit1.Text]));
    Q.Active := True;
    Total := Q.FieldByName('CNT').Asinteger;
    Q.Active := False;
  finally
    FreeAndNil(Q);
  end;
  Label1.Caption := IntToStr(Total DIV 60) + ' Hrs ' + IntToStr(Total MOD 60) + ' Mins';
end;

Method 2 - loop through Dataset
procedure TForm1.Button2Click(Sender: TObject);
var
  Total: Integer;
begin
  Total := 0;
  ADOQuery1.DisableControls;
  ADOQuery1.First;
  while not ADOQuery1.Eof do
  begin
    Total := Total + ADOQuery1.FieldByName('[Time Spent]').AsInteger;
    ADOQuery1.Next;
  end;
  ADOQuery1.DisableControls;

  Label1.Caption := IntToStr(Total DIV 60) + ' Hrs ' + IntToStr(Total MOD 60) + ' Mins';
end;

Open in new window

0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

jimyXCommented:
If you mean DBGrid then you can add calculated field of the Type String, in your DB component:

procedure TForm1.AdoTable1CalcFields(DataSet: TDataSet);
begin
  AdoTable1.FieldByName('Time').AsString := (inttostr(AdoTable1.FieldByName('TotalTime').Asinteger div 60) +' hours, '+ inttostr(AdoTable1.FieldByName('LetterNo').Asinteger mod 60) +' minutes');
end;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jimyXCommented:
Sorry typo, here is update:
procedure TForm1.AdoTable1CalcFields(DataSet: TDataSet);
begin
  AdoTable1.FieldByName('Time').AsString := (inttostr(AdoTable1.FieldByName('TotalTime').Asinteger div 60) +' hours, '+ inttostr(AdoTable1.FieldByName('TotalTime').Asinteger mod 60) +' minutes');
end;

Open in new window


BTW:
10 minutes and 90 minutes = 1 hours and 40 minutes
0
Norm-alNetwork EngineerAuthor Commented:
Yes, its a DBGrid. I now have 3 items, 2 with "10" and 1 with "40" pulling from the table and showing in the TimeSpent field on the grid. How can I now add the sums in the group/footer to sum with the calculations and show 'Hours' and 'Minutes'? What event do I link it to? I have formatted percentages before using the OnGetDisplayText but that just changes how it shows within the cell, not for the group/footer sums.
Thanks!
0
jimyXCommented:
Create a new calc column "Time" in the DB component (as attached) and link your DBGrid intended column to this "column "Time" and then add the above code at OnCalcFields of that same DB Component:
procedure TForm1.AdoTable1CalcFields(DataSet: TDataSet);
begin
  AdoTable1.FieldByName('Time').AsString := (inttostr(AdoTable1.FieldByName('TimeSpent').Asinteger div 60) +' hours, '+ inttostr(AdoTable1.FieldByName('TimeSpent').Asinteger mod 60) +' minutes');
end;

Open in new window

CalcField.JPG
0
Ephraim WangoyaCommented:

Are you using DevExpress grid
0
Norm-alNetwork EngineerAuthor Commented:
Yes, DevExpress Grid
0
Ephraim WangoyaCommented:

Got to the DataController property of the TableView, expand it and you can create summary fields from there.

Select the column and field name then specify Sum as the calculation

Summary.PNG
SummarySetupField.bmp
0
Norm-alNetwork EngineerAuthor Commented:
I have done that before for normal counts and sums but in this case, I need to know how to format the sum so that anything over 60 minutes shows as '1 Hour' so if the total sum is 90, the sum at the group or footer shows "1 Hour, 30 minutes"
0
Ephraim WangoyaCommented:
Its better to set it up with the properties but if you are adventorous and want to do it in code, you use the following code
with (AView as TcxGridDBTableView) do
      with DataController.Summary do
      begin
        BeginUpdate;
        try
          with FooterSummaryItems.Add as TcxGridDBTableSummaryItem do
          begin
            Column := Columns[2]; //index of the column you are summing
            Kind := skSum;
          end;
        finally
          EndUpdate;
        end;
      end;

Open in new window

0
Norm-alNetwork EngineerAuthor Commented:
that works but how do I format it to divide out the minutes by 60 and show it as 1 Hour?
0
Ephraim WangoyaCommented:

Let me see, I'll check it out
0
Ephraim WangoyaCommented:
Use the GetText event like this
procedure TForm1.cxGrid1DBTableView1TcxGridDBDataControllerTcxDataSummaryFooterSummaryItems0GetText(
  Sender: TcxDataSummaryItem; const AValue: Variant; AIsFooter: Boolean;
  var AText: String);
begin
  if not VarIsNull(AValue) then
    AText := Format('%d: %d', [AValue DIV 60, AValue MOD 60]);
end;

Open in new window

0
Ephraim WangoyaCommented:

Just make sure you are using the GetText event of the summary field and not of the column


procedure TForm2.cxGrid1DBTableView1TcxGridDBDataControllerTcxDataSummaryDefaultGroupSummaryItems0GetText(
  Sender: TcxDataSummaryItem; const AValue: Variant; AIsFooter: Boolean;
  var AText: string);
begin
  if not VarIsNull(AValue) then
    AText := Format('%d:%d', [AValue DIV 60, AValue MOD 60]);
end;

or

procedure TForm1.cxGrid1DBTableView1TcxGridDBDataControllerTcxDataSummaryFooterSummaryItems0GetText(
  Sender: TcxDataSummaryItem; const AValue: Variant; AIsFooter: Boolean;
  var AText: String);
begin
  if not VarIsNull(AValue) then
    AText := Format('%d %d', [AValue DIV 60, AValue MOD 60]);
end;

Open in new window

Summary.png
0
Ephraim WangoyaCommented:
I have provided the solution using the GetText method. This is how custom formating is done in devexpress fields and its the way I handle it in my applications
0
Ephraim WangoyaCommented:

Are you kidding?

I just gave you the correct answer, how does adding a calculated field to each row give you summary
0
Ephraim WangoyaCommented:

Yes I did,

The accepted solution makes no sense, where as the Author is asking about formating the summary field and I quote "grid to compute that the total for those 2 items"
Creating a calculated field (as accepted here) works on each row but produces no summary. to get the summary, you actually have to create a summary field for the grid.

I use devexpress grid all the time and I know that for sure, So I just wondered how that could have been the correct solution.
0
Geert GOracle dbaCommented:
Prec94513
You may find that any solution given for DBGrid does not work for devexpress grids

If you ask a question about a devexpress component, note so in the header of your question
Otherwise a lot of comments are waisted on trying to figure out which components you are using
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.