combine data associated with multiple date/time columns

**dannybten23** · 06-06-2013, 12:19 AM

I have 4 sets of columns. each has a date and time in column one, and a number in column two. the dates and times are close, but are not the exact same. I want to add the numbers of all the non-date columns that are closest in time. i.e. i want all the numbers that are close to noon on the 15th to be added, and all the numbers that are close to 1:00 on the 15th to be added and so on. Example attached. Any suggestions will be greatly appreciated.
Very Respectfully,
Dan

**XOR LX** · 06-06-2013, 04:02 AM

Hi Dan,

Could you be a bit more specific? What defines 'close to'? Within 30 minutes or less from each time? (So that 13:29 would be 'close to 13:00' and 13:31 'close to 14:00' - what about 13:30?)

For which values do you want to return sums? Every hour of every day (you have over 5,500 times in there covering approximately 171 different dates)?

Are comparisons to be made across all four columns, or must each restrict itself to looking in the same column, so that you want to keep the 'close to 13:00' sum for [64] - AC2 CAPACITY separate from the 'close to 13:00' sum for [24] - AC1 CAPACITY, etc.?

Please try and provide as much information as you can.

Regards

**dannybten23** · 06-10-2013, 05:11 PM

Thanks for the response. I will try to provide as much info as possible. 30 minutes is close enough. combining 1329 with 1300 is fine. 1330 could go to 1300 or 1330.

I would like to return sums for every two hours or so.

I want to sum all values that occured at the same time. So if all four columns had a value between 1300 and 1329, I would want the SUM of all four columns (as well as the number of columns that were included in the calculation, for reference).

The non-date numbers are loads. I am trying to calculate the average load.

thank you!
Dan