Forum Discussion

ask's avatar
ask
Helper III
9 years ago
Solved

Date Format

Hi,   date format  in the original data source is text format  as shown below . 30/05/2017   I want to convert it into US date format as date type. It returns error : (even though I use th...
Need Help
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    This code should work below.

     

    =[#"Day"] & "/" & [#"Month"] & "/" & [#"Year"]

    Then once you have that column created, convert the data type to Date

ask's avatar
ask
Helper III
9 years ago

what happen after refreshing the data for the second one, there will be new data coming

GilbertQ's avatar
GilbertQ
Super User
9 years ago

Hi ask

 

It will update as new data comes in because it is based on your original column. So no data or dates will be lost.

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    This code should work below.

     

    =[#"Day"] & "/" & [#"Month"] & "/" & [#"Year"]

    Then once you have that column created, convert the data type to Date

  • ask's avatar
    ask
    Helper III
    9 years ago

    I create a year column by using Add Column - Custum Column

     

    Year = right([Date],4)

     

    Expression.Error: The name 'right' wasn't recognized. Make sure it's spelled correctly.

     

    Any direction?

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    Hi ask

     

    This is because "right" is an Excel formula

     

    You can try the following:

     

    Year = Date.Year([Date])

    Your Date column must have the data type of Date

  • ask's avatar
    ask
    Helper III
    9 years ago

    i use the split column function to get the Year, month and day.

     

    i try to create a new date by using 

     

    date= datevalue(CONCATENATE((CONCATENATE([Month],[Day])),[Year])) from create Add Custom Column.

     

    It returned error:

     

    Expression.Error: The name 'datevalue' wasn't recognized.  Make sure it's spelled correctly.