I need to create an Excel query with Datalink that will take a power tag and for the month take the 1st hour of every day (30 or 31) sum the 30 first hours up and display them in the first cell the the 2nd hour of everyday for the month in the second cell and so on.

Is there a fairly ingenious way of doing this versus having to put 1st hour of every day on the spreadsheet and sum with excel?

Hi Kenneth

I'm not sure what's the nature of your power tag, so when you say "take the 1st hour of every day", do you mean taking the total of the power tag for the 1st hour? Or the power tag should have 1 single value every hour and you just need to take that value?

Depending on what you want to get for the hour of every day, we can write a PE equation for that. For example, if I wanted to find the time-weighted total of a tag within an hour from the sampling/calculating time, the PE equation would be:

Then using DataLink's

Sampled Data, we can calculate aPI Expression(the above PE equation) for the 1st hour every day within a month by sampling daily (start time= 1st day of the month at 00:00:00,end time= last day of the month at 00:00:00 andInterval= 1d). Then you can use Excel's Sum function to sum the output from Sampled Data up.Or you can also use

Calculated Data, calculating aPI Expression(the same PE equation as above). Set thestart time= 1st day of the month at 00:00:00,end time= last day of the month at 00:00:00.Calculation mode= total andCalculation basis= event-weighted (to find the sum). In addition, chooseExpression Sampling= interpolated andExpression Sampling Frequency= 1d)By defining a PE that calculates based on archive value within an hour and sampling daily, we are effectively extracting data from a specific hour within a day for calculation. The difference in the 2 options above is that the 1st option is finding the 30 or 31 day sum in Excel, whereas the 2nd option is using DataLink to do that.

You can increase the start time and end time by 1 hour to find the result for the next hour and so on.