Can anybody format the time piece in this sample of data from a PV array please

Calahonda52

The long number is time and the second, smaller number is Watts
thanks as always: it was created by an export to cvs function

1516092445 33
1516092513 77
1516092576 147
1516092640 192
1516092702 220
1516092762 192
1516092826 139
1516092891 32

Alun

Calahonda52 wrote: »

The long number is time and the second, smaller number is Watts
thanks as always: it was created by an export to cvs function

1516092445 33
1516092513 77
1516092576 147
1516092640 192
1516092702 220
1516092762 192
1516092826 139
1516092891 32

It's so called Unix time, the number of seconds since midnight 1/1/1970.

=((A1/60)/60)/24)+DATE(1970,1,1) will return the date and time (replace A1 with the cell reference) but you'll need to format the result as either date or time to see it formatted correctly and not as just a number. You an also create a custom format string to display date and time together in one cell.

1516092445 16/01/2018 08:47:25
1516092513 16/01/2018 08:48:33
1516092576 16/01/2018 08:49:36
1516092640 16/01/2018 08:50:40
1516092702 16/01/2018 08:51:42
1516092762 16/01/2018 08:52:42
1516092826 16/01/2018 08:53:46
1516092891 16/01/2018 08:54:51

Calahonda52

Alun,
as always, much obliged to you

Just looking at the data, the time interval seems close to, but not exactly a minute so I wonder how to deal with that problem.

I will post the excel sheet, which shows an overrun of 3 seconds in a 4 hour period, math at Cell A241 I 241

I tried hh:mm but it truncates rather than round

Alun

Firstly I missed a bracket off the formula I gave in the post, there should be an extra parenthesis at the start i.e. ..

=(((A1/60)/60)/24)+DATE(1970,1,1)

although you could actually miss out all of the parentheses in the first part of the formula and it would still work

=A1/60/60/24+DATE(1970,1,1)

As for the other problem, there's not a whole lot you can do about that, it's in the original data, not a fault in the formula. The device itself seems to be delivering samples at something close to, but not exactly 60 seconds, sometimes less, sometimes more for reasons that I suspect only the manufacturer will be able to explain.

Any attempt to round any figures up or down will ultimately run into problems as the error accumulates and you'll end up with sudden jumps of 2 minutes instead of 1.

Ubbquittious

Tried a few of the timestamps with the python time.localtime() function and they're spot on so nothing wrong with the formula

The stats generating could be far down the list of priorities on the controller which is why you don't get them on the same second of each minute.

Calahonda52

Thanks for this, the set up is a 11kW array, with micro inverters on each 295W panel.

Where in the setup is the controller: All i see is a little screen on the wall, all the rest of the stuff is hidden