Date formats in Excel

[Anonymous]

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	20180630	8966.02628
AXIS	20180731	8973.26532
AXIS	20180831	8980.64236
AXIS	20180930	8987.12596
AXIS	20181031	8994.45493
AXIS	20181130	9001.23626
AXIS	20181231	9010.17852
AXIS	20190131	9014.45574
AXIS	20190228	9015.25834
AXIS	20190331	9015.93442
AXIS	20190430	9016.7537
AXIS	20190531	6561.219
AXIS	20190630	6562.019
AXIS	20190731	6694.358
AXIS	20190831	6629.351
AXIS	20190930	6609.2
AXIS	20191031	6589.75
AXIS	20191130	6583.197
AXIS	20191231	6638.637
AXIS	20200131	8940.25721
AXIS	20200229	8950.96969
AXIS	20200331	8958.45176
AXIS	20200430	8966.02628
AXIS	20200531	8973.26532
AXIS	20200630	8980.64236
AXIS	20200731	8987.12596

My output table need to be like this:

AXIS	31/07/2020	Current Month
AXIS	30/06/2020	1 month
AXIS	31/05/2020	2 month
AXIS	30/04/2020	3 month
AXIS	31/03/2020	4 month
AXIS	29/02/2020	5 month
AXIS	31/01/2020	6 month
AXIS	31/12/2019	7 month
AXIS	30/11/2019	8 month
AXIS	31/10/2019	9 month
AXIS	30/09/2019	10 month
AXIS	31/08/2019	11 month
AXIS	31/07/2019	1 year
AXIS
AXIS
AXIS
AXIS
AXIS
AXIS
AXIS
AXIS
AXIS
AXIS
AXIS
AXIS	31/07/2018	2 years
AXIS
AXIS
AXIS
AXIS
AXIS
AXIS

Any help is greatly appreciated..

ara

[ryan_mayu · Accepted solution]

@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")))

[dedelman_clng]

Hi @Anonymous  - can you elaborate on what your data is and what specifically you are trying to do?  Also see here for pointers on how to get the best responses when you post a question: How to Get Your Question Answered Quickly 

David