Date Format

[jmercado]

Hello,

Im trying to Format a column in a Date format, the current column show as this 20200501 (05/01/2020).

im curretling using the formula:

Sold Date=Date(Left(DQPCDH[DCDATE],4),Right(left(DQPCDH[DCDATE],6),2),right(DQPCDH[DCDATE],2))

That Formula works Great if the table looks like the below table.

Product	Sold date 1	Sold date
Bike	20200604	06/04/2020
Ball	20200405	04/05/2020

but im getting a error due to some of the spaces are blank or Zero.

Product	Sold date 1	Sold Date
Bike	20200604	#ERROR
Ball	0	#ERROR

[v-stephen-msft · Accepted solution]

Hello @jmercado ,

You can test this calculated column.

Sold Date = IF([Sold date 1]<>0|| [Sold date 1]<>BLANK(),Date(Left([Sold date 1],4),Right(left([Sold date 1],6),2),right([Sold date 1],2)))

Best regards

Stephen Tao

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

Shouldn't that formula use && rather than ||  ?

Sold Date = IF([Sold date 1]<>0 && [Sold date 1]<>BLANK(),Date(Left([Sold date 1],4),Right(left([Sold date 1],6),2),right([Sold date 1],2)))

[CNENFRNL]

Hi, @jmercado , just set back and relax. Power Query is capable of convert such 8-digit dates with ease!

Or if you insist on doing it youself,

[mahoneypat]

You could do this pretty easily in the query editor, but if you want to make a DAX column, you can just wrap your expression in IFERROR()

Regards,

Pat