How to split list of dates into weeks

Question

I have a list of dates, that range from July 2015 to January 2016

2015/07/20 14:15:23 
2015/07/20 14:16:06 
2015/07/26 23:39:15 
2015/09/02 22:51:46 
2015/09/02 22:52:33 
2015/10/26 20:42:23 
2015/11/12 14:54:31 
2015/11/30 06:49:10 
2015/12/02 10:18:17 
2015/12/10 08:34:53 
2015/12/10 15:15:33 
2015/12/15 06:43:32 
2015/12/29 02:55:01 
2016/01/12 19:46:45 
2016/01/13 07:31:14 
2016/01/13 07:32:59 
2016/01/14 05:28:33

I want to split these up into weeks, So I know exactly how many weeks have passed by and get a count for each group. I'm pulling the dates from a webservice and putting them into a datagridview.

I'm going to add a column called week, where I cant to add the week each belongs to.

if (SplitChkBox.Checked == true)
{
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "Week";
    table.Columns.Add(column);
}

Eventually I'll create a graph off how many instances belong to each week.

Thanks!

EDIT

From 2015/07/20 there are about 25 weeks until 2016/01/14. I want to return 1-25 and the count of how many weeks belong to that group if its more than 0.

Here you can find how to get a week http://stackoverflow.com/questions/11154673/get-the-correct-week-number-of-a-given-date — Valentin, Mar 02 '16 at 22:40
I've converted the dates to ISO format, even though in this question it was clear what format was being used it is still good practice on SO to use the international standard to avoid cases of ambiguity. — Enigmativity, Mar 02 '16 at 23:39

score 5 · Accepted Answer · edited Jun 20 '20 at 09:12

It's fairly straight-forward to work out what you need, but the most complicated thing is to work out what week of the year you're looking at. The .NET framework helps you out by offering many different calendars - including many that aren't Gregorian. Most likely you can just use CultureInfo.CurrentCulture.DateTimeFormat.Calendar to get your calendar.

Then you can do this:

var results =
    dates
        .GroupBy(x => CultureInfo.CurrentCulture.DateTimeFormat.Calendar
            .GetWeekOfYear(x, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday))
        .SelectMany(gx => gx, (gx, x) => new
        {
            Week = gx.Key,
            DateTime = x,
            Count = gx.Count(),
        });

Note that the week number is based on two extra parameters for CalendarWeekRule & DayOfWeek. You can choose FirstDay, FirstFourDayWeek, FirstFullWeek for the first parameter and any day of the week for the second.

I've used the .SelectMany to flatten the results and to who you how to obtain the week number and the count, but you may need to produce your data in a different format, but this should help with calculations nonetheless.

Here are the results I got:

Based on your edit it is quite easy to make a query that groups by weeks starting on the earliest date.

var firstDay = dates.Min().Date;

var results =
    dates
        .GroupBy(x => x.Date.Subtract(firstDay).Days / 7 + 1)
        .SelectMany(gx => gx, (gx, x) => new
        {
            Week = gx.Key,
            DateTime = x,
            Count = gx.Count(),
        });

I now get this result:

I not understand what you meant by "count of how many weeks belong to that group if its more than 0".

I added an edit i want to divide the dates into about 25 weeks, one for each each starting from the earliest date to the latest date. — Sewder, Mar 03 '16 at 00:01

Arturo Menchaca · Answer 2 · 2016-03-03T15:32:56.717

If you want the number of the week in the year, you can use Calendar class:

DateTime date = new DateTime(2015, 07, 20);

DateTimeFormatInfo dateTimeFormatInfo = DateTimeFormatInfo.CurrentInfo;  // Current culture-specific information about the format of date and time.          
Calendar calendar = dateTimeFormatInfo.Calendar;                         // Calendar used in current culture.

var weekOfYear = calendar.GetWeekOfYear(date,                                // The date
                                        dateTimeFormatInfo.CalendarWeekRule, // Rule for determining the first week of the year.
                                        dateTimeFormatInfo.FirstDayOfWeek);  // Day used as the first day of week

EDIT:

You can create an extension to DateTime returning the week for easier use:

public static class DateTimeExtensions
{
    public static int GetWeekOfYear(this DateTime date)
    {
        DateTimeFormatInfo dateTimeFormatInfo = DateTimeFormatInfo.CurrentInfo;
        Calendar calendar = dateTimeFormatInfo.Calendar;

        var weekOfYear = calendar.GetWeekOfYear(date,
                                                dateTimeFormatInfo.CalendarWeekRule,
                                                dateTimeFormatInfo.FirstDayOfWeek);

        return weekOfYear;
    }
}

And groups the dates using GroupBy():

var dates = new DateTime[] { /* dates */ };

var groups = dates.GroupBy(d => d.GetWeekOfYear()).Select(g => new 
{
    Week = g.Key,
    Dates = g.ToArray(),
    Count = g.Count()
});

I would combine this with LINQ. Something like groupedDates = myDateList.GroupBy(d => myCalendar.GetWeekOfYear(d)). Then you can get the counts of each of the groups and continue processing in other ways as needed. — JamesFaix, Mar 02 '16 at 23:32

score 1 · Answer 3 · answered Mar 02 '16 at 22:59

Do you mean like this (it makes some simplifying assumptions):

List<DateTime> DateList = new List<DateTime>();

DateList.Add(DateTime.Parse("7 / 20 / 2015 2:15:23 PM"));
DateList.Add(DateTime.Parse(" 7 / 20 / 2015 2:16:06 PM"));
DateList.Add(DateTime.Parse(" 7 / 26 / 2015 11:39:15 PM"));
DateList.Add(DateTime.Parse(" 9 / 2 / 2015 10:51:46 PM"));
DateList.Add(DateTime.Parse(" 9 / 2 / 2015 10:52:33 PM"));
DateList.Add(DateTime.Parse(" 10 / 26 / 2015 8:42:23 PM"));
DateList.Add(DateTime.Parse(" 11 / 12 / 2015 2:54:31 PM"));
DateList.Add(DateTime.Parse(" 11 / 30 / 2015 6:49:10 AM"));
DateList.Add(DateTime.Parse(" 12 / 2 / 2015 10:18:17 AM"));
DateList.Add(DateTime.Parse(" 12 / 10 / 2015 8:34:53 AM"));
DateList.Add(DateTime.Parse(" 12 / 10 / 2015 3:15:33 PM"));
DateList.Add(DateTime.Parse(" 12 / 15 / 2015 6:43:32 AM"));
DateList.Add(DateTime.Parse(" 12 / 29 / 2015 2:55:01 AM"));
DateList.Add(DateTime.Parse(" 1 / 12 / 2016 7:46:45 PM"));
DateList.Add(DateTime.Parse(" 1 / 13 / 2016 7:31:14 AM"));
DateList.Add(DateTime.Parse("1 / 13 / 2016 7:32:59 AM"));
DateList.Add(DateTime.Parse("1 / 14 / 2016 5:28:33 AM"));

Dictionary<int, int> MyHistogram = new Dictionary<int, int>();

foreach(DateTime MyDateTime in DateList)
{
    int Week = MyDateTime.DayOfYear / 7;

    if (MyHistogram.ContainsKey(Week))
        MyHistogram[Week]++;
    else
        MyHistogram.Add(Week, 1);
}

I need to re-word my answer, but given the earliest date being 7/20/2015 there are 26 weeks until january 14th. Thats how I want each datetime grouped, 26 weeks. Is this possible? — Sewder, Mar 02 '16 at 23:48
@Sewder - You can pre-initialize the histogram 'buckets' with any week combinations you wish (initialize each to zero). — Steve Wellens, Mar 03 '16 at 02:43

How to split list of dates into weeks

3 Answers3