Detect Possible Date Formats - Excel, Power Query, Python Challenges

Out of 3 date formats only, MDY, DMY & YMD, list the date formats for given dates. An ambiguous dates may have more than one possible formats.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 605
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Detect Possible Date Formats with Power Query

Power Query solution 1 for Detect Possible Date Formats, proposed by Abdallah Ally:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddCol = Table.AddColumn(
    Source, 
    "My Answer", 
    each [
      a = Text.Replace([Date], "/", "-"), 
      b = {"d", "dd"}, 
      c = {"M", "MM", "MMM", "MMMM"}, 
      d = {"yy", "yyyy"}, 
      fx = (r, s, t) =>
        List.TransformMany(
          List.TransformMany(r, each s, (u, v) => u & "-" & v), 
          each t, 
          (w, x) => w & "-" & x
        ), 
      e = fx(b, c, d), 
      f = fx(c, b, d), 
      g = fx(d, c, b), 
      h = List.Zip({e, List.Repeat({"DMY"}, List.Count(e))}), 
      i = List.Zip({f, List.Repeat({"MDY"}, List.Count(f))}), 
      j = List.Zip({g, List.Repeat({"YMD"}, List.Count(g))}), 
      k = List.Transform(
        h & i & j, 
        (x) => if (try Date.FromText(a, [Format = x{0}]))[HasError] then null else x{1}
      ), 
      l = Text.Combine(List.Distinct(List.RemoveNulls(k)), ", ")
    ][l]
  ), 
  Result = Table.AddColumn(AddCol, "Check", each [Answer Expected] = [My Answer])
in
  Result

Power Query solution 2 for Detect Possible Date Formats, proposed by Seokho MOON:

let
  Com = [Y = {"yy", "yyyy"}, M = {"M", "MMM"}, D = {"d"}, S = {"-", "/"}], 
  Func = (x as list) as list =>
    [
      T = List.Transform(x, each List.Transform(_, each {_})), 
      A = List.Accumulate(
        {1 .. List.Count(T) - 1}, 
        T{0}, 
        (a, v) => List.TransformMany(a, (y) => T{v}, (y, z) => y & z)
      ), 
      R = List.Transform(A, each Text.Combine(List.RemoveLastN(_), List.Last(_)))
    ][R], 
  Format = [
    DMY = Func({Com[D], Com[M], Com[Y], Com[S]}), 
    MDY = Func({Com[M], Com[D], Com[Y], Com[S]}), 
    YMD = Func({Com[Y], Com[M], Com[D], Com[S]})
  ], 
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Res = Table.AddColumn(
    Source, 
    "Answer Expected", 
    (x) =>
      [
        T = List.Transform(
          Record.FieldNames(Format), 
          (y) =>
            [
              A = List.Accumulate(
                Record.Field(Format, y), 
                {}, 
                (a, v) => a & {(try Date.FromText(x[Date], [Format = v]))[HasError]}
              ), 
              R = if List.AllTrue(A) then null else y
            ][R]
        ), 
        F = Text.Combine(List.RemoveNulls(T), ", ")
      ][F]
  )
in
  Res

Power Query solution 3 for Detect Possible Date Formats, proposed by Mihai Radu O:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  s = Table.AddColumn(
    Source, 
    "r", 
    each [
      f = {"MDY", "DMY", "YMD"}, 
      z = {"%d", "dd", "ddd", "dddd", "%M", "MM", "MMM", "MMMM", "%y", "yy", "yyy", "yyyy"}, 
      p = List.Transform(
        z, 
        (x) => List.Distinct(Text.ToList(Text.Upper(Text.Select(x, {"a" .. "z", "A" .. "Z"})))){0}
      ), 
      tb = List.Zip({p, z}), 
      a = Text.SplitAny([Date], "-/"), 
      b = List.Transform(
        a, 
        (x) =>
          List.Distinct(
            List.RemoveNulls(
              List.Transform(
                tb, 
                (t) =>
                  try if Date.FromText(x, [Format = t{1}]) is date then t{0} else null otherwise null
              )
            )
          )
      ), 
      c = List.Combine(
        List.Transform(
          {0 .. 2}, 
          (x) =>
            List.Combine(
              List.Transform(
                b{0}, 
                (_a) =>
                  List.Combine(
                    List.Transform(b{1}, (_b) => List.Transform(b{2}, (_c) => {_a, _b, _c}))
                  )
              )
            )
        )
      ), 
      d = List.Distinct(List.Transform(c, Text.Combine)), 
      e = Text.Combine(List.Select(d, (x) => List.Contains(f, x)), ", ")
    ][e]
  )[r]
in
  s

Solving the challenge of Detect Possible Date Formats with Excel

Excel solution 1 for Detect Possible Date Formats, proposed by Bo Rydobon 🇹🇭:

=MAP(A2:A10,LAMBDA(d,LET(f,{"DMY","MDY","YMD"},
ARRAYTOTEXT(FILTER(f,1-ISNA(XMATCH(f,REDUCE("",--TEXTSPLIT(d,{"/","-"}),LAMBDA(a,v,LET(b,DROP({"Y","D","M"},,IFERROR(1-MATCH(v,{0,13,32}),2)),TOCOL(a&b)))))))))))

Excel solution 2 for Detect Possible Date Formats, proposed by John V.:

=MAP(A2:A10,
    LAMBDA(x,
    LET(i,
    TEXTSPLIT(
        x,
        {"-";"/"}
    ),
    s,
    SEQUENCE(
        3,
        3
    ),
    ARRAYTOTEXT(FILTER({"DMY";"MDY";"YMD"},
    BYROW(IF(ISERR(
        -i
    ),
    IF((s=2)+(s=4)+(s=8),
    MONTH(
        i&1
    )),
    i*{1,
    1,
    -1;1,
    1,
    -1;-1,
    1,
    1}<{32,
    13,
    0;13,
    32,
    0;0,
    13,
    32}),
    AND))))))

Excel solution 3 for Detect Possible Date Formats, proposed by Timothée BLIOT:

=LET(A,
    SEQUENCE(
        12
    ),
    B,
    TEXT(
        DATE(
            1900,
            A,
            1
        ),
        "MMM"
    ),
    C,
    REDUCE(
        A2:A10,
        UPPER(
            B
        ),
        LAMBDA(
            w,
            v,
            SUBSTITUTE(
                w,
                v,
                XLOOKUP(
                    v,
                    B,
                    A
                )
            )
        )
    ),
    MAP(C,
    LAMBDA(x,
    LET(D,
    --REGEXEXTRACT(
        x,
        "d+",
        1
    ),
    M,
    TAKE(
        D,
        ,
        1
    ),
    N,
    INDEX(
        D,
        ,
        2
    ),
    O,
    TAKE(
        D,
        ,
        -1
    ),
     H,
    LAMBDA(m,
    n,
    o,
    p,
     LET(d,
    XLOOKUP(
        p,
        {1,
        2,
        3},
        {"YYMMDD",
        "DDMMYY",
        "MMDDYY"}
    ),
     Y,
    IF(
        p=1,
        m,
        o
    ),
    V,
    DATE(
        IF(
            Y<100,
            2000+Y,
            Y
        ),
        IF(
            p=1,
            n,
            IF(
                p=2,
                n,
                m
            )
        ),
        IF(
            p=1,
            o,
            IF(
                p=2,
                m,
                n
            )
        )
    ),
    w,
    LAMBDA(
        x,
        TEXT(
            MOD(
                x,
                100
            ),
            "00"
        )
    ),
    ISNUMBER(
        V
    )*(TEXT(
        V,
        d
    )=w(
        m
    )&w(
        n
    )&w(
        o
    )))),
    
TEXTJOIN(
    ",",
    ,
    IF(
        H(
            M,
            N,
            O,
            1
        ),
        "YMD",
        ""
    ),
    IF(
        H(
            M,
            N,
            O,
            2
        ),
        "DMY",
        ""
    ),
    IF(
        H(
            M,
            N,
            O,
            3
        ),
        "MDY",
        ""
    )
)))))

Excel solution 4 for Detect Possible Date Formats, proposed by Eddy Wijaya:

=REDUCE(B1,A2:A10,LAMBDA(a,v,VSTACK(a,LET(
sp,TEXTSPLIT(v,{"-","/"}),
c,MAP(sp,LAMBDA(m,IFERROR(MONTH(DATEVALUE(m&1))&";",--m))),
tx,MAP(c,LAMBDA(d,IFS(ISTEXT(d),"m:",
LEN(d)<=2,IFS(d<=12,"m",d<=31,"d",TRUE,"y"),
TRUE,"y"))),
f,CONCAT(tx),
IFS(ISNUMBER(SEARCH(":",f)),CONCAT(MAP(tx,LAMBDA(m,IFS(m="m:","M",m="m","D",TRUE,"Y")))),
LEFT(f,3)="mmm","DMY, MDY, YMD",
LEFT(f,2)="mm","DMY, MDY",
RIGHT(f,2)="mm","YMD",
FIND("md",f),UPPER(f),
TRUE,"")))))

Excel solution 5 for Detect Possible Date Formats, proposed by Philippe Brillault:

=LET(n,SEQUENCE(ROWS(_P)),VLOOKUP(REDUCE(_T,n,LAMBDA(t,i,REGEXREPLACE(t,INDEX(_P,i,1),INDEX(_P,i,2)))),_Cd,2,0))

Solving the challenge of Detect Possible Date Formats with Python

Python solution 1 for Detect Possible Date Formats, proposed by Abdallah Ally:

import pandas as pd
from itertools import product
# Create required data manipulation functions 
def zip_values(format, *values):
 products = ['/'.join(x) for x in product(*values)]
 results = list(zip(products, [format] * len(products)))
 return results
def get_date_styles():
 d = ['%d']
 m = ['%m', '%b', '%B']
 y = ['%y', '%Y']
 dmy = zip_values('DMY', d, m, y)
 mdy = zip_values('MDY', m, d, y)
 ymd = zip_values('YMD', y, m, d)
 return dmy + mdy + ymd
def get_date_formats(str_date):
 date_styles = get_date_styles()
 date_formats = []
 for style in date_styles:
 try:
 format = pd.to_datetime(str_date.replace('-', '/'), format=style[0])
 date_formats.append(style[1])
 except: ''
 return ', '.join(date_formats)
df = pd.read_excel(file_path, usecols='A:B')
# Perform data manipulation
df['My Answer'] = df['Date'].map(get_date_formats)
df['Check'] = df['Answer Expected'] == df['My Answer']
df

Solving the challenge of Detect Possible Date Formats with Python in Excel

Python in Excel solution 1 for Detect Possible Date Formats, proposed by Alejandro Campos:

from datetime import datetime
df = xl("A1:A10", headers=True)
def check_formats(date_str):
 valid_formats = []
 for fmt in ["%m/%d/%Y", "%m-%d-%Y", "%b-%d-%y", "%b-%d-%Y"]:
 try:
 datetime.strptime(date_str, fmt)
 valid_formats.append("MDY")
 break
 except:
 pass
 for fmt in ["%d/%m/%Y", "%d-%m-%Y", "%d-%b-%y", "%d-%b-%Y"]:
 try:
 datetime.strptime(date_str, fmt)
 valid_formats.append("DMY")
 break
 except:
 pass
 for fmt in ["%Y-%m-%d", "%y-%m-%d", "%Y-%b-%d", "%y-%b-%d"]:
 try:
 datetime.strptime(date_str, fmt)
 valid_formats.append("YMD")
 break
 except:
 pass
 return ", ".join(valid_formats) if valid_formats else "Invalid"
df['Answer Expected'] = df['Date'].apply(check_formats)
df

Python in Excel solution 2 for Detect Possible Date Formats, proposed by Anshu Bantra:

from datetime import datetime
import re
def infer_date_formats(date_str):
 # List of ambiguous date formats to test
 date_str = re.sub(r'[-/._s]', '-', str(date_str))
 possible_formats = {
 "%d-%m-%y": "DMY",
 "%d-%m-%Y": "DMY",
 "%d-%b-%y": "DMY",
 "%d-%b-%Y": "DMY",
 "%d-%B-%y": "DMY",
 "%d-%B-%Y": "DMY",
 
 "%m-%d-%Y": "MDY",
 "%m-%d-%y": "MDY",
 "%B-%d-%y": "MDY",
 "%B-%d-%Y": "MDY",
 "%b-%d-%y": "MDY",
 "%b-%d-%Y": "MDY",
 "%y-%d-%b": "YDM",
 "%Y-%d-%b": "YDM",
 "%y-%d-%B": "YDM",
 "%Y-%d-%B": "YDM",
 "%Y-%m-%d": "YMD",
 "%y-%m-%d": "YMD",
 }
 
 valid_formats = []
 
 for fmt, label in possible_formats.items():
 try:
 datetime.strptime(date_str, fmt)
 valid_formats.append(label)
 except ValueError:
 continue
 return ', '.join(valid_formats)
df = xl("A1:A10", headers=True)
df['Answer'] = df['Date'].apply(infer_date_formats)
df

Solving the challenge of Detect Possible Date Formats with R

R solution 1 for Detect Possible Date Formats, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
library(lubridate)
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")
 all_formats = c("DMY", "MDY", "YMD")
 parts = str_split(date_str, "[^A-Za-z0-9]+")[[1]]
 letter_parts = which(str_detect(parts, "[A-Za-z]"))
 if (length(letter_parts) > 0) {
 month_pos = letter_parts[1]
 format_positions = list("DMY" = 2, "MDY" = 1, "YMD" = 2)
 all_formats = names(format_positions)[sapply(format_positions, function(pos) pos == month_pos)]
 }
 valid_formats = all_formats[sapply(all_formats, function(fmt) {
 parsed_date = switch(fmt, "DMY" = dmy(date_str), "MDY" = mdy(date_str), "YMD" = ymd(date_str))
 !is.na(parsed_date)
 })]
 if (length(valid_formats) == 0) NA else paste(valid_formats, collapse = ", ")
}

&&&

Solving the challenge of Detect Possible Date Formats with Power Query

Solving the challenge of Detect Possible Date Formats with Excel

Solving the challenge of Detect Possible Date Formats with Python

Solving the challenge of Detect Possible Date Formats with Python in Excel

Solving the challenge of Detect Possible Date Formats with R

Leave a Reply Cancel reply