How to summarise data recorded in milliseconds into other time periods?

[Ruede]

Hi,

I would greatly appreciate any suggestions about how to transform a large data file containing values recorded in milliseconds (custom time format: d.mm.yyyy hh:mm:ss.000), to a separate data table showing the average of these values for other longer time periods (eg. seconds, minutes, hours etc).

My original data recorded in milliseconds looks like this:
[TABLE="width: 371"]
<tbody>[TR]
[TD]Time (milliseconds)[/TD]
[TD]decay1[/TD]
[TD]decay2[/TD]
[/TR]
[TR]
[TD]10.10.2017 00:00:00.285[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]10.10.2017 00:00:00.441[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]10.10.2017 00:00:01.595[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]10.10.2017 00:00:01.861[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]10.10.2017 00:00:01.992[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]

I want to transform it into a separate data file that reports (for example) the average of the values recorded during each one second period in the original table above:
[TABLE="width: 371"]
<tbody>[TR]
[TD]Time (seconds)[/TD]
[TD]decay1[/TD]
[TD]decay2[/TD]
[/TR]
[TR]
[TD]10.10.2017 00:00:00[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]10.10.2017 00:00:01[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

I've looked through previous forum posts and elsewhere about pivot tables but couldn't find a solution to this.

Thanks in advance,

Ruede

 

[pgc01]

Hi

A pivot table will give you directly the result you want.

Just select the table, Insert->Pivot Table and use average on the 2 decay fields.

I'm assuming that the first column is real excel date-time values.

 

[Ruede]

Thanks for the reply PGC. I tried what you suggest but as you cautioned, Excel (I'm using vers 2016) doesn't read the first column as date-time values. I've tried formatting this as a custom type (ie. d.mm.yyyy hh:mm:ss.fff) but no dice - the resulting pivot table gives no option to group the data by the various periods (eg. seconds, minutes, hours etc). I've also tried formating this column using preset Date (eg. d.mm.yyyy) or Time (eg. hh:mm:ss) formats but that does not work. I'm guessing that's because the values in this column do not correspond with that format?

Is there a way to make Excel treat the first column of values in a d.mm.yyyyy hh:mm.ss.fff format as real date-time variables?

 

[pgc01]

Hi

You say you've used several formats, but format is just for humans to see things in a nice way.
If you change the format of a cell, its value does not change, it's just displayed in another way.

If I understand correctly, you have string values in the first column. You have to convert them into datetime values.

If the format of the string in the first column is always like in the examples you posted "dd.mm.yyyy hh:mm:ss.000" then use in another column:

=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))+MID(A1,12,12)

and copy down.

This column will now have datetime values and you can format it the way you want.

Use it as the first column in your table and try the Pivot Table.

 

[Ruede]

Thanks for the date function lesson PGC. It works!

Could you explain why you included the last MID function (ie. that reads "MID(A!,12,12) ) ? The output seems to be the same whether that function is included or not??

 

[pgc01]

I'm glad it worked

Could you explain why you included the last MID function (ie. that reads "MID(A!,12,12) ) ? The output seems to be the same whether that function is included or not??

That's the time that you have to add to the date in the first part of the formula, or else you'd just get the day, not the seconds.

Try both options, with and without the MID() and format the cell with "yyyy-mm-dd hh:mm:ss.000".

 

[Ruede]

Thanks again PGC - am beginning to see the logic behind this.

One last question (for now!):

I created a column that stores TIME information with the formula =TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,5)). When selected as a row in a pivot table it gives me the option to group data in hours and minutes.

For the sake of parsimony I tried to create a column that stores both the DATE as well as TIME values using:
=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,5))

While Excel dutifully produces a column with both date and time info, when I use it in a pivot table it gives no option to group my data by hours and minutes - only day and date. It appears that Excel reads just the DATE part and ignores the TIME component from this particular column.

Is it possible to create a column of date/time values that when imported as a row in a pivot table, gives options to group data by hour and minute, as well as day and date?

 

[pgc01]

Hi

2 points

1 - when I try with datetime and insert a pivot table, it gives me all the options for grouping, from seconds to years. I don't know why you don't get the same option.
I'm testing with excel 2010.

2 - your formula is not correct as far as the last part is concerned, the MID(A1,18,5)

for ex., for "10.10.2017 00:00:00.285", MID(A1,18,5) results in "00.28" with which

- you lose the milliseconds in the string that results from the MID(), maybe you wanted MID(A1,18,6) which would result in "00.285"

- the TIME() function expects an integer in the third parameter and so you lose all the second decimals. Since you just get just integer seconds you can use "MID(A1,18,2)" instead.

If you also want to get the milliseconds you can use the formula I posted or, with your formula, add them at the end, for ex.: "+MID(A1,21,3)*TIME(0,0,1)/1000"

=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))+MID(A1,21,3)*TIME(0,0,1)/1000

Notice that the formula that I posted before would also get the milliseconds that are recognised by excel in the string->datetime conversion

=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))+MID(A1,12,12)

 

[Ruede]

Many thanks for the detailed explanation PGC. That is really helpful.

Will work through/figure out what I'm doing that is preventing Excel from giving me all the pivot table options to group by date down to seconds.