Date Cipher Decryption - Excel, Power Query, Python & R Challenge

DATE CIPHER DECRYPTER – Decrypt the given messages which were encrypted using following logic. Date Cipher is a type of Caesar’s shift cipher where key is current date in YYYYMMDD format. In this challenge, I am fixing up the dates in column B rather than using current date. Date will be made equal to text by repeating the date. If text is shorter than date, then use only that many characters from date from beginning as many are in text. Hence, if text is “superbexcel” and date is 20230530, then key will be 20230530202. 20230530 repeated 2 times and taking only 11 characters from this as text has only 11 characters. Letters will be shifted by corresponding number in the date. Hence, s by 2, u by 0, p by 2, e by 3, r by 0, b by 5 and so on. Hence, answer would be uurhrghxeen

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

Solving the challenge of Date Cipher Decryption with Power Query

Power Query solution 1 for Date Cipher Decryption, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Return = Table.AddColumn(
    Source, 
    "Answer", 
    each [
      M = [Message], 
      KT = Text.From([Key]), 
      ML = Text.Length(M), 
      RPC = Number.RoundUp(ML / 8), 
      RP = Text.Repeat(KT, RPC), 
      K = Text.Start(RP, ML), 
      LM = Text.ToList(M), 
      LK = Text.ToList(K), 
      Z = List.Zip({LM, LK}), 
      T = List.Transform(
        Z, 
        (f) =>
          [
            n = Character.ToNumber(f{0}) - 96, 
            m = Number.Mod(26 + n - Number.From(f{1}), 26), 
            c = Character.FromNumber(96 + m)
          ][c]
      ), 
      R = Text.Combine(T)
    ][R]
  )
in
  Return

Power Query solution 2 for Date Cipher Decryption, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.AddColumn(
    Source, 
    "Answer", 
    (x) =>
      let
        a = Text.ToList(x[Message]), 
        b = List.Transform(a, each Character.ToNumber(_) - 97), 
        c = List.Transform(Text.ToList(Text.From(x[Key])), Number.From), 
        d = 
          if List.Count(b) <= List.Count(c) then
            List.FirstN(c, List.Count(b))
          else
            List.FirstN(c & c & c, List.Count(b)), 
        e = List.Transform(List.Zip({b, d}), each Number.Mod(26 + _{0} - _{1}, 26) + 97), 
        f = List.Transform(e, Character.FromNumber)
      in
        Text.Combine(f)
  )[[Answer]]
in
  Sol

Solving the challenge of Date Cipher Decryption with Excel

Excel solution 1 for Date Cipher Decryption, proposed by Bo Rydobon 🇹🇭:

=MAP(A2:A7,B2:B7,LAMBDA(a,k,LET(s,SEQUENCE(LEN(a)),c,CODE(MID(a,s,1)),f,FLOOR(c,32),
CONCAT(CHAR(MOD(c-MID(REPT(k,9),s,1)-f,26)+f)))))

Excel solution 2 for Date Cipher Decryption, proposed by Rick Rothstein:

Excel solution 3 for Date Cipher Decryption, proposed by Rick Rothstein:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(s,SEQUENCE(LEN(a)),c,CODE(MID(a,s,1))-MID(LEFT(b&b&b,LEN(a)),s,1),CONCAT(CHAR(26*(c<97)+c)))))

Excel solution 4 for Date Cipher Decryption, proposed by John V.:

Excel solution 5 for Date Cipher Decryption, proposed by محمد حلمي:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(
v,SEQUENCE(LEN(a)),CONCAT(CHAR(MOD(
CODE(MID(a,v,1))-MID(b&b&b,v,1)-97,26)+97)))))

Excel solution 6 for Date Cipher Decryption, proposed by Kris Jaganah:

=MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(a,SEQUENCE(LEN(x)),b,MID(x,a,1),c,MID(y,a,1),d,MOD(a,8),CONCAT(CHAR((MOD(CODE(b)-INDEX(c,IF(d=0,8,d))-96,26)+96))))))

Excel solution 7 for Date Cipher Decryption, proposed by Julian Poeltl:

=MAP(A2:A7,B2:B7,LAMBDA(M,K,LET(L,LEN(M),KY,LEFT(REPT(K,L),L),S,SEQUENCE(L),C,CODE(MID(M,S,1))-MID(KY,S,1),CONCAT(CHAR(IF(C<97,C+26,C))))))

Excel solution 8 for Date Cipher Decryption, proposed by Timothée BLIOT:

=MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(M,SEQUENCE(LEN(x)),A,CODE(MID(x,M,1)),B,MID(y,MOD(M-1,8)+1,1),CONCAT(CHAR(MOD(A-B-97,26)+97)))))

Excel solution 9 for Date Cipher Decryption, proposed by Nikola Z Grujicic - Nikola Ž Grujičić:

=MAP(A2:A7,B2:B7,LAMBDA(a, b, LET(g, LEN(a), h, LEN(b), i, IF(g96,f-n,f-n+26), TEXTJOIN("",,UNICHAR(o)))))

Excel solution 10 for Date Cipher Decryption, proposed by Oscar Mendez Roca Farell:

=MAP(
    A2:A7,
     B2:B7,
     LAMBDA(
         a,
          b,
          LET(
              F,
               LAMBDA(
                   i,
                    MID(
                        i,
                         SEQUENCE(
                             LEN(
                                 i
                             )
                         ),
                         1
                    )
               ),
               CONCAT(
                   CHAR(
                       96 + TOROW(
                           MOD(
                               WRAPCOLS(
                                   CODE(
                                       F(
                                           a
                                       )
                                   )-96,
                                    8
                               )-F(
                                   b
                               ),
                                26
                           ),
                            2,
                            1
                       )
                   )
               )
          )
     )
)

Excel solution 11 for Date Cipher Decryption, proposed by Sunny Baggu:

=MAP(
 A2:A7,
 B2:B7,
 LAMBDA(x, y,
 LET(
 s, SEQUENCE(LEN(x)),
 _a, MID(x, s, 1),
 _b, MID(REPT(y, 3), s, 1),
 _c, CODE(_a) - _b,
 _d, IF(_c < 97, _c + 26, _c),
 CONCAT(CHAR(_d))
 )
 )
)

Excel solution 12 for Date Cipher Decryption, proposed by Abdallah Ally:

Excel solution 13 for Date Cipher Decryption, proposed by Abdallah Ally:

=MAP(A2:A7,
    B2:B7,
    LAMBDA(x,
    y,
    LET(a,
    x,
    b,
    y,
    c,
    LEN(
        a
    ),
    d,
    LEFT(
        REPT(
            b,
            CEILING(
                c/LEN(
                    b
                ),
                1
            )
        ),
        c
    ),
    REDUCE("",
    SEQUENCE(
        c
    ),
    LAMBDA(x,
    y,
    LET(e,
    CODE(
        MID(
            a,
            y,
            1
        )
    )-MID(
        d,
        y,
        1
    ),
    x&CHAR(e+26*(e<97))))))))

Excel solution 14 for Date Cipher Decryption, proposed by Pieter de B.:

=LET(
    y,
    --TEXTSPLIT(
        TEXTAFTER(
            ","&B3:B10,
            ",",
            {1,
            2,
            3}
        ),
        ","
    ),
    x,
    LAMBDA(
        z,
        TOCOL(
            IFS(
                y,
                z
            ),
            2
        )
    ),
    SORT(
        HSTACK(
            x(
                y
            ),
            x(
                A3:A10
            )
        ),
        ,
        -1
    )
)

Excel solution 15 for Date Cipher Decryption, proposed by Asheesh Pahwa:

Excel solution 16 for Date Cipher Decryption, proposed by Andy Heybruch:

=MAP(A2:A7,B2:B7,LAMBDA(_m,_k,LET(
_lenM,LEN(_m),
_lenK,LEN(_k),
_key,LEFT(REPT(_k,ROUNDUP(_lenM/_lenK,0)),_lenM),
_m1,MID(_m,SEQUENCE(_lenM),1),
_k1,MID(_key,SEQUENCE(_lenM),1),
CONCAT(CHAR(MOD(CODE(_m1)-_k1-97,26)+97)))))

Excel solution 17 for Date Cipher Decryption, proposed by Sandeep Marwal:

=BYROW(A2:B7,
LAMBDA(a,LET(p,TAKE(a,,1),q,TAKE(a,,-1),
REDUCE("",SEQUENCE(LEN(p)),
LAMBDA(a,v,CONCAT(a,CHAR(
LET(x,CODE(MID(p,v,1))-MID(LEFT(REPT(q,ROUNDUP(LEN(p)/LEN(q),0)),LEN(p)),v,1),
IF(x<97,26+x,x))
)))))))

Excel solution 18 for Date Cipher Decryption, proposed by Edwin Tisnado:

Excel solution 19 for Date Cipher Decryption, proposed by El Badlis Mohd Marzudin:

=MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(a,CODE(REGEXEXTRACT(x,"w",1)),b,REGEXEXTRACT(IF(LEN(y)>LEN(x),y,REPT(y,999)),"w",1),c,TOROW(a-b,3),d,CHAR(IF(c<97,c+26,c)),CONCAT(d))))

Excel solution 20 for Date Cipher Decryption, proposed by Hussain Ali Nasser:

=MAP(A2:A7, B2:B7, LAMBDA(m,k, LET(l, LEN(m), k, LEFT(REPT(k, 10), l), ks, MID(k, SEQUENCE(, l), 1), s, MID(m, SEQUENCE(, l), 1), c, CODE(s), m, MOD(c - ks, 122), ch, CHAR(IF(m < 97, m + 26, m)), CONCAT(ch))))

Excel solution 21 for Date Cipher Decryption, proposed by Bevon Clarke:

=MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(seq,SEQUENCE(LEN(x)),b,MID(x,seq,1),e,MID(REPT(y,3),seq,1),v,CODE(b)-e,o,IF(v<97,v+26,v),CONCAT(CHAR(o)))))

Excel solution 22 for Date Cipher Decryption, proposed by Tyler Cameron:

Solving the challenge of Date Cipher Decryption with Python

Python solution 1 for Date Cipher Decryption, proposed by Konrad Gryczan, PhD:

import pandas as pd
path = "492 Date Shift Cipher Decrypter.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows = 7)
test = pd.read_excel(path, usecols="C", nrows = 7)
def decrypt_date_cipher(text, date):
 repeat_date = (str(date) * ((len(text) // len(str(date))) + 1))[:len(text)]
 text_nums = [ord(char.lower()) - ord('a') for char in text]
 key_nums = [int(char) for char in repeat_date]
 decrypted_nums = [(text_num - key_num + 26) % 26 + ord('a') for text_num, key_num in zip(text_nums, key_nums)]
 decrypted_text = ''.join(chr(num) for num in decrypted_nums)
 return decrypted_text
input["Answer Expected"] = input.apply(lambda x: decrypt_date_cipher(x[0], x[1]), axis=1)
print(input["Answer Expected"].equals(test["Answer Expected"])) # True

Solving the challenge of Date Cipher Decryption with Python in Excel

Python in Excel solution 1 for Date Cipher Decryption, proposed by Abdallah Ally:

import pandas as pd
from math import ceil
def date_shift_cipher_decrypter(text, date): 
 date = str(date)
 shift = (date * ceil(len(text) / len(date)))[ : len(text)]
 decrypted_text = ''
 for key, char in enumerate(text):
 code = ord(char) - int(shift[key])
 decrypted_text += chr(code + 26 * (code < 97))
 return decrypted_text
file_path = 'Excel_Challenge_492 - Date Shift Cipher Decrypter.xlsx'
df = pd.read_excel(file_path)
# Perform data wrangling
df['My Answer'] = df.apply(
 lambda x: date_shift_cipher_decrypter(x['Message'], x['Key']), axis=1
)
df['Check'] = df['My Answer'] == df['Answer Expected']
df

Solving the challenge of Date Cipher Decryption with R

R solution 1 for Date Cipher Decryption, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
path = "Excel/492 Date Shift Cipher Decrypter.xlsx"
input = read_xlsx(path, range = "A1:B7")
test = read_xlsx(path, range = "C1:C7")
decrypt_date_cipher <- function(text, date) {
 key <- str_sub(str_c(rep(date, ceiling(nchar(text) / nchar(date))), collapse = ""), 1, nchar(text))
 text_nums <- utf8ToInt(str_to_lower(text)) - utf8ToInt("a")
 key_nums <- key %>%
 str_split("") %>%
 flatten_chr() %>%
 as.integer()
 decrypted_nums <- map2_int(text_nums, key_nums, ~ (.x - .y + 26) %% 26 + utf8ToInt("a"))
 decrypted_text <- intToUtf8(decrypted_nums)
 return(decrypted_text)
}
output = input %>%
 mutate(`Answer Expected` = map2_chr(Message, Key, decrypt_date_cipher))
identical(output$`Answer Expected`, test$`Answer Expected`) 
#> [1] TRUE

Solving the challenge of Date Cipher Decryption with Power Query

Solving the challenge of Date Cipher Decryption with Excel

Solving the challenge of Date Cipher Decryption with Python

Solving the challenge of Date Cipher Decryption with Python in Excel

Solving the challenge of Date Cipher Decryption with R

Leave a Reply Cancel reply