Calculation of sum of hours in 24h format with incorrect C#

Question

I have a mistake in calculating the hours. The values you see below are taken from a database and my task with C # is to do the sum of the hours.

I list the problems below:

1) Total wrong hours,

2) As you can see in the data grid view there is a time with 9.7 hours but it is impossible that there are 70 minutes in an hour! I place below the C # code and the relative data grid view, can you help me to solve it?

DataGridView Value:

24-07-2018  9:7     08:30
25-07-2018  0:0     08:30 
26-07-2018  9:42    08:30

Final sum-> 18:49    1:30

C# Code:

     private void buttonCarica_Click(object sender, EventArgs e)
     {
       dataGridViewPrincipale.Hide();
       dataGridViewSecondario.Hide();
       resetDataGrid();
       string StartDate = dateTimePickerInizio.Value.Date.ToString("dd-MM-yy");
       string EndData = dateTimePickerFine.Value.Date.ToString("dd-MM-yy");
       Utente utemp = new Utente(comboBoxUtenti.Text);
       timemanager tm = new timemanager(utemp);
       PeopleController r = new PeopleController(utemp);
       DataTable dt = tm.CaricaDataeTotaleHour(StartDate, EndData);

       if (dt.Rows.Count == 0)
       {
         reset();
         MessageBox.Show("\n ATTENZIONE:  " + comboBoxUtenti.Text + " in questo range di tempo non ha effettuato marcature");
       }
       else
       {
         labelTotaleHourWork.Show();
         labelTotaleHourWorkMod.Show();
         labelTotaleHourtimemanagerMod.Show();
         labelTotaletimemanager.Show();
         dataGridViewPrincipale.Show();
         TimeSpan tempoSpanTotaleHourWork = TimeSpan.Zero;

         foreach (DataRow dr in dt.Rows)
         {
           String TotaleHourWork = r.CaricaHourGiornaliere(dr["Data"].ToString());
           if (TotaleHour.Equals("0") == false)
           {
             TotaleHourWork = TotaleHourWork.Replace(",", ":");
             tempoSpanTotaleHourWork = SommaHour(TotaleHourWork, tempoSpanTotaleHourWork);
           }

           if (tempoSpanTotaleHourWork == TimeSpan.Zero)
           {
             TotaleHourWork = "0";
           }

           String[] row = { dr["Data"].ToString(), "" + tm.ConteggioHourGiornaliere(dr["Data"].ToString()), "" + TotaleHourWork };
           dataGridViewPrincipale.Rows.Add(row);
         }

         labelTotaleHourtimemanagerMod.Text = "" + tm.GetTotaleHourRange();
         labelTotaleHourWorkMod.Text = "" + CheckValue.ConversioneTimeSpantoString(tempoSpanTotaleHourWork);
         }
       }

private TimeSpan SommaHour(String t1, TimeSpan t2)
{
  t1= TotaleOreCantiere.ToString().Replace(",", ":");
  tempoSpanTotaleOreCantiere = tempoSpanTotaleOreCantiere + TimeSpan.Parse(t2);
  return tempoSpanTotaleOreCantiere;
}

Function CaricaHourGiornaliere :

public String CaricaHourGiornaliere(String Data)
{
  String ret = "0";
  DataTable dt = RisorseUmaneModel.CaricaHourGiornaliere(Data);

  foreach (DataRow dr in dt.Rows)
  {
    ret = dr["Ore"].ToString();
  }

  return ret;
}

--RisorseUmaneModel--

public static DataTable CaricaHourGiornaliere ( String Data)
{
  String Query = " SET LANGUAGE 'Italian' SELECT CONVERT(VARCHAR(10), People.Data, 105) as DataIns,''+Utente.Nome+' '+Utente.Cognome as Risorsa,DATENAME(WEEKDAY, People.Data) as Giorno,Cliente.RagioneSociale,Working.NomeWorking,convert(varchar(5), Cast(convert(varchar(5), (OreFine - OreInizio), 108) as datetime) - CAST(REPLACE(Pausa, '.', ':') as datetime), 108) as Ore FROM People inner join Working on Working.IdWorking = People.IdWorking inner join Cliente on Working.IdCliente = Cliente.IdCliente inner join Utente on Utente.IdUtente=People.IdUtente  where People.IdUtente = @IdUtente and(People.Data between @Start and @End) order by CONVERT(DateTime, People.Data,101)  asc";
  SqlCommand cmd = new SqlCommand(Query, conn, tran);
  cmd.Parameters.AddWithValue("@IdUtente", SqlDbType.Int).Value = 1;
  cmd.Parameters.AddWithValue("@Start", SqlDbType.DateTime).Value = Data;
  cmd.Parameters.AddWithValue("@End", SqlDbType.DateTime).Value = Data;
  SqlDataAdapter da = new SqlDataAdapter(cmd);
  da.Fill(dt);

  return dt
}

Function CheckValue.ConversioneTimeSpantoString :

public static String ConversioneTimeSpantoString(TimeSpan tm)
{
      return "" + tm.Hours.ToString().Replace("-", "").ToString() + ":" + tm.Minutes.ToString().Replace("-", "").ToString();
}

@DmitryBychenko the result as you say it should be 25:30 but it comes 1:30 but I can not understand why — riki, Jul 26 '18 at 08:52
Try providing formats explisitly, e.g. instead of `String TotaleHourWork = r.CaricaHourGiornaliere(dr["Data"].ToString())` put `String TotaleHourWork = r.CaricaHourGiornaliere(dr["Data"].ToString("d\\.hh\\:mm\\:ss"))` see https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-timespan-format-strings — Dmitry Bychenko, Jul 26 '18 at 09:01
@DmitryBychenko if I use your code on toString it gives me an error before compiling and it says that No overload method for ToString take1 arguments — riki, Jul 26 '18 at 09:13
`what is dr["Data"]` type? I.e. what is `dr["Data"].GetType()` result? — Dmitry Bychenko, Jul 26 '18 at 09:14
@DmitryBychenko dr["Data"] is a string that is taken from the database, waiting for the code that picks up the value from the db — riki, Jul 26 '18 at 09:17
Show us what's inside : CheckValue.ConversioneTimeSpantoString please — Jimbot, Jul 26 '18 at 09:19

score 1 · Accepted Answer · answered Jul 26 '18 at 09:50

1

In your function public static String ConversioneTimeSpantoString(TimeSpan tm)

Change

tm.Hours //-> the remining hours in this timespan (minus days)

To

tm.TotalHours //-> the sum of all hours in this timespan (included days, months, years)

answered Jul 26 '18 at 09:50

Jimbot

696
5
20

I tried your solution but the hours are no longer calculated correctly – riki Jul 26 '18 at 10:09

score 0 · Answer 2 · answered Jul 26 '18 at 09:28

0

TotaleHourWork.Replace(",", ":");

"9:7" means 9 hours, 7 minutes. "9,7" probably means 9,7 hours, which is 9 hours and 42 minutes.

So whatever you think this does, it does not. It's just corrupting most of your data.

Stop doing string replacements, take your date, parse it into TimeSpans properly and then calculate your numbers using those.

answered Jul 26 '18 at 09:28

nvoigt

75,013
26
93
142

I don't understand what you mean by that. – nvoigt Jul 26 '18 at 10:02
it is possible to convert (7) into 42 minutes @nvoigt – riki Jul 26 '18 at 10:03
I don't know... why would you want to? You could do `TimeSpan.FromHours(0.7)` but if you need to do that you should take a minute and make sure you are doing the right thing. – nvoigt Jul 26 '18 at 10:05
I can not change the query is already used for other things, so I have to try to understand the minutes well – riki Jul 26 '18 at 10:07
I have really no idea why it's so complicated. "9:7" seems like a fine data point. Parse it into a TimeSpan, add them all up, done. [I just don't understand your logic because all your variable names are gibberish to me](https://meta.stackoverflow.com/questions/266563/do-non-english-words-increase-the-probability-of-receiving-downvotes/) – nvoigt Jul 26 '18 at 10:11

Calculation of sum of hours in 24h format with incorrect C#

2 Answers2