Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Date formats in Excel

Hi,   I have an Excel datasheet as below   AXIS 20180131 2342.35 AXIS 20180228 34124.24 AXIS 20180331 8940.25721 AXIS 20180430 8950.96969 AXIS 20180531 8958.45176 AXIS...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    Anonymous 

    try to create two columns

    Date = date(LEFT('Table'[Column2],4),mid('Table'[Column2],5,2),RIGHT('Table'[Column2],2))

Month = 
VAR mth=DATEDIFF('Table'[Date],max('Table'[Date]),MONTH)
return if(mth=0,"CurrentMonth",if(mth/12<1,mth&" month",if(mth/12<2,"1 year",int(mth/12)&" years")))

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
6 years ago

Anonymous 

try to create two columns

Date = date(LEFT('Table'[Column2],4),mid('Table'[Column2],5,2),RIGHT('Table'[Column2],2))

Month = 
VAR mth=DATEDIFF('Table'[Date],max('Table'[Date]),MONTH)
return if(mth=0,"CurrentMonth",if(mth/12<1,mth&" month",if(mth/12<2,"1 year",int(mth/12)&" years")))