Calculate average date difference of multiple rows

Question

I need to calculate the average of the date difference of all of my rows in dataGridView.

I implemented NodaTime (which is far more easy than the traditional methods to calculate date difference) and, I did this just to try out:

var date1 = new LocalDate(2013, 1, 29);
var date2 = new LocalDate(2018, 1, 23);
Period period = Period.Between(date1, date2.PlusDays(1));
label1.Text = string.Format("{0} anos, {1} meses e {2} dias", period.Years, 
period.Months, period.Days);

Now, what I do to get both dates of the dataGridView, calculate in each row the date difference and with all calculate the average? I want it to show in years, months and days. Thanks.

I mean, calculate the date difference of 2 columns in each row — CaldeiraG, Mar 13 '18 at 14:11
@TiagoCaldeira so you basically want to calculate date difference between 2 dateTimes? — styx, Mar 13 '18 at 14:12
Well, for difference, I'd add a [calculated column](https://stackoverflow.com/q/6097370/982149). Then you can [sum that column](https://stackoverflow.com/q/3779729/982149) (see also the not accepted answer https://stackoverflow.com/a/3779835/982149) and divide by count of rows... — Fildor, Mar 13 '18 at 14:20
Makes sense but I have no idea how to implement that. Basically i want to look at how much time does it take to an article move out from the storage (for example) EDIT: just looked that you attached some links, will check it out! — CaldeiraG, Mar 13 '18 at 14:25
@TiagoCaldeira - Hi, I've posted a solution.. hope it helps to some extent! — Wheels73, Mar 13 '18 at 15:19

score 3 · Accepted Answer · answered Mar 13 '18 at 19:05

Unfortunately there's no real concept of an average of Period values, as they're not even directly comparable. For example, is "1 month" longer or shorter than "29 days"? It depends on the month. Moving to an average, what's the of the two periods "1 month" and "29 days"? There's nothing really obvious as a useful answer to that question, IMO.

What you could do is get the difference just in days (probably using Period.Between and specifying PeriodUnits.Days) - then find the average number of days. That makes logical sense and is a lot more easily defined than the average of years/months/days.

score 0 · Answer 2 · answered Mar 13 '18 at 15:17

I had to resolve a similar issue for one of our projects. I've knocked up the below code snippet which seems to work.

Assuming you have a grid on a form for the purpose of this test.

Add the dates to your grid columns. I use columns 0 and 1 for the 2 dates.

private void Form1_Load(object sender, EventArgs e)
{
        dgGridView.Rows.Add(new DataGridViewRow());
        dgGridView.Rows.Add(new DataGridViewRow());
        dgGridView.Rows.Add(new DataGridViewRow());

        dgGridView.Rows[0].Cells[0].Value = "Feb 01 2018 00:00:00";
        dgGridView.Rows[0].Cells[1].Value = "Feb 03 2018 06:00:45";
        dgGridView.Rows[1].Cells[0].Value = "Feb 02 2018 17:00:00";
        dgGridView.Rows[1].Cells[1].Value = "Feb 03 2018 21:54:21";
        dgGridView.Rows[2].Cells[0].Value = "Feb 04 2017 10:00:00";
        dgGridView.Rows[2].Cells[1].Value = "Feb 07 2018 08:23:26";
}

The under a button click or whatever mechanism you choose, calculate the averages. This is done by calculating the seconds that have elapsed between each date range and then divide it by the number of rows.

var totalSeconds = 0.0;

 foreach (DataGridViewRow row in dgGridView.Rows)
 {
    var date1 = Convert.ToDateTime(row.Cells[0].Value);
    var date2 = Convert.ToDateTime(row.Cells[1].Value);
    var diff = (date2 - date1).TotalSeconds;

    row.Cells[2].Value = diff;
    totalSeconds += diff;
}

var totalRows = 3;
var aveSeconds = totalSeconds / totalRows;

TimeSpan aveTime = TimeSpan.FromSeconds(aveSeconds);

var totDays = aveTime.Days;
var totHours = aveTime.Hours;
var totMins = aveTime.Minutes;
var totSeconds = aveTime.Seconds;

var ave = $"Days:{totDays} Hours:{totHours} Mins:{totMins} Seconds:{totSeconds}";

You can then get a TimeSpan object from those total seconds and extract the number of days, hours, minutes and seconds for the average.

I think this works. Apologies in advance if some eagle eyed person spots a flaw, but I hope it helps you on your way.

Thanks

This looks promising and actually can work but i wanted in years, months and days, the hours are not important.. — CaldeiraG, Mar 13 '18 at 15:21
@TiagoCaldeira - Well if the number of days >= 365 then I guess you can interpret the years? — Wheels73, Mar 13 '18 at 15:24
It's partially working yeah but I would like accurate results, not just divide by 365 — CaldeiraG, Mar 13 '18 at 16:09

Calculate average date difference of multiple rows

2 Answers2