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

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.

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

    This code should work below.

     

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

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

  • 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.