CODEWITHSUNDEEP
excelpowerquery
InteressantPunt
InteressantPunt

Reputation: 145

Power Query - Create date from datenumber and time column

In Excel's powerquery editor I have two columns. One is a daynumber (1 to 365) and the other is the time. I would like to merge the two columns to a datetime column. Day 1 will correspond with 01-01-2000. How can I do this?

For example:

enter image description here

Upvotes: 0

Views: 670

Answers (2)

Olly
Olly

Reputation: 7891

You can use this to add a column which combines the relative date and time values:

= Table.AddColumn(#"Changed Type", "DateTime", each DateTime.FromText(Date.ToText(Date.AddDays(#date(1999,12,31),[#".1"])) & " " & Time.ToText([#".2"], "hh:mm:ss")), type datetime)

Check your logic for the date of Day 0, though - if day 1 is 01/01/2000 then day 365 <> 31/12/2000...

Upvotes: 1

Andreas
Andreas

Reputation: 23968

Excel dates are built up on float values where the integer is the number of days passed since 1900-01-01 and the fraction is the time of day.

With that in mind 2000-01-01 is 36526.

With the formula: 

=36526+A1+B1

You get the datetime value, then just set the prefered cell format

enter image description here

With this formula 365 + 02:00:00 will become as you expect in question.
But 1 can't be 2000-01-01 then, it has to be 2000-01-02. Or you need to specify where to remove a day becasuse the math does not add up.

Upvotes: 0

Related Questions