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

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

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.