How to get row count for half an hour interval from a Excel with a column type DateTime

Hi Team, I have a excel file with a column in DateTime format. It has data for different dates with time. I want to get the row count based on every 30 minute interval. For example: If there are 3 rows between 8/3/2021 19:00 - 8/3/2021 19:29 the output should be written in the next sheet as “8/3/2021 19:00 - 8/3/2021” in column “Range” and “3” in column “Count”. Attaching Sample file, can someone please help me to solve this?
sampleFile.xlsx (165.2 KB)

As a quick prototype approaching the case have we can do:


(From d In dtData.AsEnumerable
Let dp = CDate(d("Start Date").toString)
Let sg = {0,30}.toList.FindLastIndex(Function (x) dp.Minute >= x)
Group d By k1=dp.toString("MM/dd/yyyy HH"), k2=sg Into grp=Group
Let sl = {"I", "II"}(k2)
Let ra = New Object(){k1  & " - " & sl, grp.Count}
Select dtReport.Rows.Add(ra)).CopyToDataTable

Sample input/output

Afterwards we can adopt e.g. on

  • DateParsing
  • Customization on Group Label

Hi @ppr Thank you so much for the response! I tried to implement the above steps but getting error, sir. Please find the attachment below. Not sure if it is because of the Read column activity output data type which is not a Datatable

find starter help here:
GroupBy_1Col_HalfHourSegmenting.xaml (9.1 KB)

Also check your variable datatypes

Hi @ppr Thank you soo much!!! this worked like a charm! Now I’m able to get the values for 30 minutes interval as expected! thanks a lot sir!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.