none
How to Convert Unix time stamp to excel time RRS feed

  • Question

  • Hi All,

    I am having a query with regard to converting unix time stamp to excel time stamp in power query.

    I have the data connection from google finance for a stock where the converted table has Date, High, low, close, open and volume data.

    I have a code which works fine only for 5 days data or till next holiday but does not work for data beyond holidays and weekend.

    The reason is after each holiday or Weekend the base value changes i.e first line will have a155.... as date time stamp in Unix with intervals after that and there will be one more a156... after the holiday or weekend which is creating issue for me.

    All this data is continuously present in the date column and need to convert them irrespective of where the next change a157... is present in the column.

    Could any one help me the code for this power query.

    Thanks,

    Nirmal

    Wednesday, May 2, 2018 7:09 AM

Answers

  • is there any identifier of the rows like 155.../156... (possibly in different columns)?
    in general you can calculate Excel date from the timestamp you have with following formula:
    = #date(1970,1,1) + #duration(0,0,0,1525272867)
    does it work if you apply it before removing the prefix?
    Wednesday, May 2, 2018 3:00 PM

All replies

  • can you share an example of the input you have and the output you expect?
    Wednesday, May 2, 2018 8:54 AM
  • Hi,

    I am not able to share the image or link as it is asking for account verification, so please provide me a mail id and i can send you the file and link of data source.

    Thanks,

    Nirmal


    Wednesday, May 2, 2018 12:32 PM
  • can you just paste here he example of the values? some for working days, some for weekends/holidays
    if you just want to et rid of a155/a156 prefixes you can use Text.Middle in PowerQuery:
    Text.Middle([UnixTimeStamp], NrOfFirstShownCharacter, Text.Length([UnixTimeStamp]))

    Wednesday, May 2, 2018 12:52 PM
  • Hi Marcin,

    Thanks for your reply, i can do a replace function in power query to get rid of the prefix.

    I am looking for following thing, once that prefix is removed the data looks like below

    155....

    1

    2

    3

    4 and so on, say some 500 lines and at line 501 it will be 156.. and line 502 will have again 1, and so on

    156...

    1

    2

    3

    so now when i want to convert it into time stamp i have a code which will add 155 +1 , 155+2, 155+3 ... so on keeping 155 as base. But when it comes to line 501 the base should change to 156+1, 156+2, 156+3 and convert them into time stamp.

    I will send you the exact code which i have in next 3-4 hours as i do not have access to code and file right now.

    Thanks

    Nirmal


    Wednesday, May 2, 2018 1:13 PM
  • is there any identifier of the rows like 155.../156... (possibly in different columns)?
    in general you can calculate Excel date from the timestamp you have with following formula:
    = #date(1970,1,1) + #duration(0,0,0,1525272867)
    does it work if you apply it before removing the prefix?
    Wednesday, May 2, 2018 3:00 PM
  • Previously answered here:

    https://social.technet.microsoft.com/Forums/en-US/7a141fa1-4b2f-42ce-8e54-b90be781074e/calculating-date-from-unix-timestamp-in-power-query?forum=powerquery

    Wednesday, May 2, 2018 3:37 PM