Forum Discussion
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 | 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
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")))
2 Replies
- dedelman_clng
Community Champion
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
ryan_mayuSuper User
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")))
