Forum Discussion

wnicholl's avatar
wnicholl
Resolver II
3 years ago
Solved

Converted Date Field (Incorrect Day Showing)

I converted source data field (yyyymmdd) "ACEFDT" using the following formula to create a calculated column to show (mm/dd/yyyy).  The calculated column show the year and month as being corrcet, but ...
Need Help
  • wnicholl's avatar
    wnicholl
    3 years ago

    I was able to resolve my date issue. The below process was  used in power query. Thanks again for your insights. 

     

    1) Changed source field to TEXT
    2) Created a custom column and used M-code // Text.BeforeDelimiter([ACEFDT], " ")) //
    3) Changed new column to Date format

grantsamborn's avatar
grantsamborn
Solution Sage
3 years ago

Try this:

Effective Date = DATE(LEFT(AISCVGP[ACEFDT],4),MID(AISCVGP[ACEFDT],5,2),RIGHT(AISCVGP[ACEFDT],2))

 

 

  • wnicholl's avatar
    wnicholl
    Resolver II
    3 years ago

    Thanks for the reply.  Unfortunately the formaula is not working for me. 

     

    • grantsamborn's avatar
      grantsamborn
      Solution Sage
      3 years ago

      Hi wnicholl 

      Sorry about that.  How about this?

       

      Effective Date = 
DATE(
	VALUE( LEFT( AISCVGP[ACEFDT], 4) ),
	VALUE( MID( AISCVGP[ACEFDT], 5, 2) ),
	VALUE( RIGHT( AISCVGP[ACEFDT], 2) )
)

       

      • wnicholl's avatar
        wnicholl
        Resolver II
        3 years ago

        I was able to resolve my date issue. The below process was  used in power query. Thanks again for your insights. 

         

        1) Changed source field to TEXT
        2) Created a custom column and used M-code // Text.BeforeDelimiter([ACEFDT], " ")) //
        3) Changed new column to Date format