Convert YYYY-DD-MM HH:MM:SS to Days

I've extracted data from a power grid website and the time stamp corresponding to each power value is in the format YYYY-DD-MM HH:MM:SS on Excel. I need to convert this to days but am struggling. The starting point is 2016-01-01 00:00:01 where 2016-01-01 00:00:00 will be 0 days and 2016-01-31 23:55:02 is the final time which will be 30.9965 Days. I could do this manually but there's roughly 10,000 of these so I will need to use an equation.

sample of spreadsheet

Anyone know how to convert to days?

Ellis Stannard

Posted 2016-02-09T16:13:28.220

Reputation: 23

Answers

With data in A1, in B1 enter:

=DATE(2016,1,31)+TIME(23,55,2)-A1

and apply a reasonable format to B1:

Gary's Student

Posted 2016-02-09T16:13:28.220

Reputation: 15 540

Like this?

The cell B3 and the others with the calc in are formatted as a number to 4 decimal places.

LLJ

Posted 2016-02-09T16:13:28.220

Reputation: 35

Your approach is right, but you misread the question. The reference point is 2016-01-01 00:00:00. The first time to compare falls one second later (that's the one you're using here as a reference point). Also, if you want times as short as 1 second to show up in the result, you probably want to display 5 or 6 decimal places. If you correct your answer, flag me in a comment and I'll upvote. – fixer1234 – 2016-02-09T20:29:35.793