Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to convert dates from data source Active Directory 1.32242E+17 to dd/mm/yyyy

Hi

 

I've been asked to create an AD report. But when I import the following field; accountexpires, lastlogon, lastlogonTimestamp, pwdLastSet, they appear in a format such as 1.32242E+17.

 

How do I convert them to dd/mm/yyyy?

 

Thanks in advance

Martin

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi v-zhenbw-msft 

     

    Thank you for your response.

     

    I solved this by using this formula:

     

    Last Log In Date = COALESCE(DIVIDE ( [user.lastLogon], 8.64 * 10 ^ 11 ) - 109205,0)
     
    Thanks
     
    Martin

3 Replies

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    In the data source, is the type a Date/Time format or scientific notation?

    In Power Query Editor, please delete the change Type, then what is the result of the column? Is it a yyyy/mm/dd hh:mm:ss type?

     

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi v-zhenbw-msft 

       

      Thank you for your response.

       

      I solved this by using this formula:

       

      Last Log In Date = COALESCE(DIVIDE ( [user.lastLogon], 8.64 * 10 ^ 11 ) - 109205,0)
       
      Thanks
       
      Martin
  • Matt-Butler's avatar
    Matt-Butler
    Regular Visitor
    2 years ago

    A better solution is to just change the format to date / time.