Excel Question about Filling down and Converting Date Time

sandyman80

I've started downloading my electricity and gas usage and it is 73000 lines long in this format

epochTimestamp	kWh	dateTime
1.65E+09	0	2022-04-11T00:00:00.000Z
1.65E+09	0	2022-04-11T00:30:00.000Z
1.65E+09	0.139	2022-04-11T01:00:00.000Z
1.65E+09	0.066	2022-04-11T01:30:00.000Z
1.65E+09	0.05	2022-04-11T02:00:00.000Z

How do I convert the Epoch time column to date time without having to fill down 73000 rows?

01/01/2022 00:00
01/01/2022 00:30
01/01/2022 01:00
01/01/2022 01:30
01/01/2022 02:00
01/01/2022 02:30

I know how to convert epoch time to 01/01/2022 00:00 ( =(A2 / 86400) + 25569 in column D) but I have to fill down 73000 rows, there must be an easier way to do this without all this filling down, then copy paste the values from column D to column E then delete columns A and C and D to get workable date times for a pivot table?

itsthelittlethings

Right click the top of column A/B/C whatever to select. Then choose the date format as appropriate.

JSmithy45AD

73,000 lines long? Have you started downloading from the start of time itself?

Woodstok2000

https://forums.moneysavingexpert.com/discussion/comment/82023881#Comment_82023881

It's smart meter data with readings every 30 mins, so it's only 4 years worth (or 2 years if gas and electric are on different lines)

outtatune

Click anywhere in the data and CTRL-t to insert a table. When you enter a formula in your first row it should then automatically populate right down to the last row.

sandyman80

https://forums.moneysavingexpert.com/discussion/comment/82023944#Comment_82023944

And I still have to fill down 73000 rows

sandyman80

https://forums.moneysavingexpert.com/discussion/comment/82023956#Comment_82023956

That doesn't work I get this

epochTimestamp	kWh	dateTime
1649635200	100	2022-04-11T00:00:00.000Z			11/04/2022 00:00
1649637000	0	2022-04-11T00:30:00.000Z
1649638800	0.139	2022-04-11T01:00:00.000Z
1649640600	0.066	2022-04-11T01:30:00.000Z
1649642400	0.05	2022-04-11T02:00:00.000Z

noitsnotme

Open Copilot within the spreadsheet and ask it to do it for you.

sandyman80

https://forums.moneysavingexpert.com/discussion/comment/82024096#Comment_82024096

I don't have Copilot in it, this version of excel is 2019

wongataa

https://forums.moneysavingexpert.com/discussion/comment/82024082#Comment_82024082

Select cell with formula. Copy cell.

Select empty cell below formula. Scroll to bottom row using scroll bar. Will take hardly any time. Shift click cell in last row in column of the formula. All cells now selected that need formula. Paste formula into cells. 73000 cells filled in seconds.

IOWJJBTM2025

Just had a play hope this helps