how to convert character datatype to date?

[Ujjwal]

Hi ,
I have to calculate YTD of numbers, but my month column is in character format, I tried coverting it in to date but it shows "Error" in every cell.
Please help me in coverting the data type.

[v-juanli-msft]

Hi @Ujjwal

How does your month column look like, "2018/3/3" or "March"?

If your column is just text value (like March) instead of a date( like 2018/3/3), converting to date type doesn't work.

Otherwise, you could consider another way to work around the problem, to find a solution which meets your scenario, please share with me your data (month column or other column can get date from).

Best Regards

Maggie

[Ujjwal]

Hi @v-juanli-msft

Month name is text value (like Mar), 

Year	Month	Amount_USD
2017	Jan	1000
2017	Feb	1200
2017	Mar	34234
2017	Apr	34356
2017	May	234556
2017	Jun	23435
2017	Jul	5880
2017	Aug	97347
2017	Sep	8290
2017	Oct	78284
2017	Nov	13940
2017	Dec	12345

[v-juanli-msft · Accepted solution]

Hi @Ujjwal

1.Select Home->edit queries,

2.Add column->Index column(from 1)

3.click on "Year" and "Index" column, then cilck on Add column->Merged

4.click on "Merged" column, then click on Add column->Format->add suffix, type value="-01"

5.transform the "Custom" column to "date" type

Best Regards

Maggie