make date from no

[cosminc]

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

[ChrisMendoza · Accepted solution]

@cosminc,

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.

[cosminc]

Hi Chris,

very helpful and we're close, thanks a lot!

there are only 2 situation to solve:

1. when we have for example 12112018 (len=8)

2. 1112018 can be 11 jan and also 1 nov or  1212018 - can be 1 dec or 21 jan; in addition to this and i think very useful is that i obtained another column in which i had a date close to the date which we have to extract (not necessarily the same)

ex

1112018      1/13/2018

1212018       11/30/2018

Can you please digg on this please?

Thanks,

Cosmin

[ChrisMendoza]

@cosminc,

My demonstration should help you break the date into pieces so you can properly make into an actual date value.

If your data is in such a way that sometime 'month' comes before 'day' or vice versa that would be a very special case. How do you know that is the case by the way? I do not know of a way to find these ambigious cases.

[cosminc]

Hi

It's fantastic your demonstration

further i doubled these columns which you created, i create a column with difference between the date from the 1st and the date available (which is close +-7days) and after that i create conditional column when the difference is <-7 & >+7, the final date will be the second option (date created by you v2) else the first date created by you (v1)

you helped me very very much!

Thanks!

Cosmin

[dedelman_clng]

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 ?

[cosminc]

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