Excel BI Challenge Workout 181

Workouts Excel BI Challenge
1

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 181 —
(Solutions in any language are also welcome for Excel Challenges)

Convert DMY text dates to MM/DD/YYYY format.

Post answers in Comment.

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

Download Practice File - https://lnkd.in/djedQGzR

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

image
image800×524 113 KB

Excel BI’s LinkedIn post:

2

LinkedIn Post by:
Taeyong Shin

All other formulas do not work in Korea.

=MAP(A2:A10, LAMBDA(m, LET( s, TEXTSPLIT(m, {"/","-"}), IFERROR(TEXT(--IF(OR(s > "9999"), m, TEXTJOIN("-", , INDEX(s, {3,2,1}))), "mm/dd/e"), ""))))

3

LinkedIn Post by:
Zoran Milokanović

Converting text dates w/ #powerquery. #bitanbit #powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.TransformRows(Source, each
Date.ToText(
try Date.FromText([DMY], [Format = "dd/M/yy"]) otherwise
try Date.FromText([DMY], [Format = "dd/M/yyyy"]) otherwise
try Date.FromText([DMY], [Format = "dd/M/yyyy"]) otherwise
try Date.FromText([DMY], [Format = "d-M-yyyy"]) otherwise
try Date.FromText([DMY], [Format = "d-MMM-yy", Culture="en-US"]) otherwise
try Date.FromText([DMY], [Format = "dd-MM-yy"]) otherwise
try Date.FromText([DMY], [Format = "d/MMMM/yy", Culture="en-US"]) otherwise
null, [Format="MM/dd/yyyy", Culture="en-US"]
)
)
in
Solution
5

LinkedIn Post by:
محمد حلمي
 

=LET(
I,MAP(A2:A10,LAMBDA(A,LET(
E,TEXTSPLIT(A,{"/","-"})+0,
N,DROP(E,,2),
RIGHT(0&IFERROR(INDEX(E,,2),MONTH(A)),2)
&"/"&
RIGHT(0&TAKE(E,,1),2)
&"/"&
IF(LEN(N)=4,N,IF(N+0>50,19&N,20&N))))),
IF(LEFT(I,2)+0>12,"",I))
7

LinkedIn post by:
Bo Rydobon

This one should work with both M/D/Y and D/M/Y setting

=MAP(A2:A10,LAMBDA(z,LET(t,TEXTSPLIT(z,,{"/","-"}),TEXT(TAKE(t,1)&TEXT(INDEX(t,2)&-99,"mmm")&DROP(t,2),"mm/dd/e;;;"))))

8

LinkedIn post by:
Guillermo Arroyo

Power Query

let
 Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
 a = Table.AddColumn(Origen, "Expected Answer", each Date.ToText(Date.FromText([DMY], [Culture="es-ES"]), [Format="MM/dd/yyyy", Culture="us-US"])),
 b = Table.ReplaceErrorValues(a, {{"Expected Answer", null}})
in
 b