Forum Discussion
Solved
make date from no
Hi all, i have an issue: how can i create date from these: where 112018 = 1st jan 2018 6152018 = 15 jun 2018 11112018 = 11 nov 2017 thanks in advance! Cosmin
This is not elegant and I'm hoping there is another solution that is...
Using:
date 112018 6152018 11112018 In the Query Editor ( Add Column > Custom Column) ,
YearNumTEXT = Text.End ( Number.ToText ( [date] ) , 4 )
DayNumTEXT = if Text.Length ( Number.ToText ( [date] ) ) = 6
then Text.Middle ( Number.ToText ( [date] ), 1, 1)
else if Text.Length ( Number.ToText ( [date] ) ) = 7
then Text.Middle ( Number.ToText ( [date] ), 1, 2)
else Text.Middle ( Number.ToText ( [date] ), 2, 2)MonthNumTEXT = if Text.Length ( Number.ToText ( [date] ) ) = 6
then Text.Start ( Number.ToText ( [date] ), 1)
else if Text.Length ( Number.ToText ( [date] ) ) = 7
then Text.Start ( Number.ToText ( [date] ), 1)
else Text.Start ( Number.ToText ( [date] ),2 )DateFromText = Date.FromText ( Text.Combine ( { [MonthNumTEXT], [DayNumTEXT], [YearNumTEXT] },"/") )That should allow you to format as Data Type Date.
Any chance you can get the source data cleaned up to be a standard 8 digit MMDDYYYY ? As it currently sits I don't see a way to parse the number into month, day and year.
For example: 1122018 - would this be 12 Jan 2018 or 2 Nov 2018 ?
Hi,
no but in addition i have a column with a date close to that:
1112018 1/13/2018
1212018 11/30/2018
Thanks,
Cosmin
