Date column + Time Column

[Sri_phani]

Hi, 

I have a Date column and another column which has End time in Whole number. I want to combine both so that I get DateTime format (DD/MM/YYYY HH:MM:SS AM/PM). 

Sample Data: 

Expected data: 

I am doing this in excel by dividing End column/86400 and then combining using text function.

Do we have a way of doing it in Power Query?

[Anonymous]

Hi @Sri_phani ,

Thanks for all the replies!
Please check these replies to see if there is a solution you are looking for. If a user's reply can help you, please don't forget to accept its reply as solution so that other users can find the solution faster, thank you!

Best Regards,
Dino Tao

[Omid_Motamedise]

Hi @Sri_phani 

Use the following formula

Date.From([Date])&Time.From(Number.From([End])/(24*60*60))

[ronrsnfld · Accepted solution]

The entry in your first row makes no sense. If your time component represents Seconds, the time component of the first row is over ten days, yet your expected data shows the result to be on the same day.

If you really want to ignore multiple days in the End column, then you can use in your custom column formula:

=DateTime.From(Number.From([Date]) + Number.Mod([End],86400)/86400)

If you really want to keep the dates where End implies more than one day, then remove the Number.Mod function:

=DateTime.From(Number.From([Date]) +[End]/86400)

Also, note that, unlike Excel, you cannot specify a particular format for a Date column in Power Query (You can in a Power BI visual). In Power Query, the appearance will be that specified in your computer Windows Regional Settings.

If you really require the format to be as you show in your Excel expected output example, you may need to convert it to a text string. Of course, if you do that, date-related math operations will be much more difficult.

Here's an example:

=DateTime.ToText(DateTime.From(Number.From([Date]) +[End]/86400), "dd/MM/yyyy hh:mm tt")

[Anonymous]

In your custom column formula, write

= Date&Time.From([End]/86400)

--Nate

[Sri_phani]

@Anonymous I am getting error when I use division method. Some of the values are creating error. Example like this, when converting 932852 into date/time format