Home » Encrypt Using Date Cipher

Encrypt Using Date Cipher

DATE CIPHER 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: 207
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Encrypt Using Date Cipher with Power Query

Power Query solution 1 for Encrypt Using Date Cipher, proposed by Omid Motamedisedeh: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  C1 = Table.AddColumn(
    Source, 
    "Custom", 
    each Text.Combine(
      List.Transform(
        {1 .. Text.Length([Message])}, 
        (x) =>
          {"a" .. "z", "a" .. "z"}{
            Character.ToNumber(Text.Range([Message], x - 1, 1))
              + Number.From(
                Text.ToList(
                  Text.Start(
                    Text.From([Key]) & Text.From([Key]) & Text.From([Key]), 
                    Text.Length([Message])
                  )
                ){x - 1}
              )
              - 97
          }
      )
    )
  )
in
  C1
Power Query solution 2 for Encrypt Using Date Cipher, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.AddColumn(
    Source, 
    "Ans", 
    each Text.Combine(
      List.Transform(
        {0 .. Text.Length([Message]) - 1}, 
        (n) =>
          Character.FromNumber(
            Number.Mod(
              Character.ToNumber(Text.Range([Message], n, 1))
                + Number.From(Text.Range(Text.Repeat(Text.From([Key]), 9), n, 1))
                - 97, 
              26
            )
              + 97
          )
      )
    )
  )
in
  Ans
Power Query solution 3 for Encrypt Using Date Cipher, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  Solution = Table.TransformRows(
    Source, 
    each 
      let
        m = Text.ToList([Message]), 
        k = List.Transform(Text.ToList(Text.From([Key])), Number.From)
      in
        Text.Combine(
          List.Accumulate(
            List.Positions(m), 
            {}, 
            (s, c) =>
              s
                & {
                  {"a" .. "z"}{Number.Mod(Character.ToNumber(m{c}) - 97 + k{Number.Mod(c, 8)}, 26)}
                }
          ), 
          ""
        )
  )
in
  Solution
Power Query solution 4 for Encrypt Using Date Cipher, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  NewCol = Table.AddColumn(
    Source, 
    "Answer", 
    each Text.Combine(
      let
        a  = _, 
        b  = Text.From([Key]), 
        b1 = Number.RoundUp(Text.Length([Message]) / Text.Length(b)), 
        c  = Text.ToList(Text.Start(Text.Repeat(b, b1), Text.Length([Message]))), 
        e  = List.Transform(Text.ToList([Message]), each Character.ToNumber(_) - 97), 
        f  = List.Transform({0 .. List.Count(e) - 1}, each Number.Mod(e{_} + Number.From(c{_}), 26)), 
        g  = List.Transform(f, each Character.FromNumber(_ + 97))
      in
        g, 
      ""
    )
  )[[Answer]]
in
  NewCol

Solving the challenge of Encrypt Using Date Cipher with Excel

Excel solution 1 for Encrypt Using Date Cipher, proposed by Bo Rydobon 🇹🇭: 
=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(s,SEQUENCE(LEN(a)),CONCAT(CHAR(MOD(CODE(MID(a,s,1))-97+MID(REPT(b,9),s,1),26)+97)))))
Excel solution 2 for Encrypt Using Date Cipher, proposed by Rick Rothstein: 
=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(d,REPT(b,1+INT(LEN(a)/8)),REDUCE("",SEQUENCE(LEN(a)),LAMBDA(t,x,t&CHAR(97+MOD(CODE(MID(a,x,1))-97+MID(d,x,1),26)))))))
Excel solution 3 for Encrypt Using Date Cipher, proposed by John V.: 
=MAP(A2:A7,B2:B7,LAMBDA(m,k,LET(n,SEQUENCE(LEN(m)),CONCAT(CHAR(97+MOD(CODE(MID(m,n,1))+MID(k&k&k,n,1)-97,26))))))
Excel solution 4 for Encrypt Using Date Cipher, 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 5 for Encrypt Using Date Cipher, proposed by Kris Jaganah: 
=MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(a,x,b,LEN(a),c,LEFT(REPT(y,3),b),d,SEQUENCE(b),e,CODE(MID(a,d,1))+MID(c,d,1),CONCAT(CHAR(IF(e>122,e-122+96,e))))))
Excel solution 6 for Encrypt Using Date Cipher, 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 7 for Encrypt Using Date Cipher, proposed by Hussein SATOUR: 
=MAP(A2:A7,B2:B7, LAMBDA(x,y, LET(
a, CODE(MID(x, SEQUENCE(LEN(x)), 1)) +
TAKE(MID(REPT(y,3), SEQUENCE(LEN(y)*3), 1), LEN(x)), CONCAT(CHAR(IF(a<123, a, a-26))))))
Excel solution 8 for Encrypt Using Date Cipher, proposed by Sunny Baggu: 
=MAP(
 A2:A7,
 B2:B7,
 LAMBDA(a, b,
 LET(
 _len, LEN(a),
 _mkey, LEFT(REPT(b, 3), _len),
 _e1, LAMBDA(x, MID(x, SEQUENCE(LEN(x)), 1)),
 _code, CODE(_e1(a)) + _e1(_mkey),
 CONCAT(CHAR(IF(_code > 122, _code - 122 + 96, _code)))
 )
 )
)
Excel solution 9 for Encrypt Using Date Cipher, proposed by JvdV -: 
=BYROW(REPT(A2:B7,{1,3}),LAMBDA(x,CONCAT(CHAR(MOD(MMULT(CODE(MID(x,SEQUENCE(LEN(@x)),1))-48,{1;1})-49,26)+97))))
Excel solution 10 for Encrypt Using Date Cipher, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert: 
=LET(b;B2:B7;g;A2:A7;k;CHAR(ROW(A97:A122));MAP(LET(j;LEN(g)-(ROUNDDOWN(LEN(g)/LEN(b);0)*LEN(b));g;REPT(b;ROUNDDOWN(LEN(g)/LEN(b);0));IFERROR(IF(j=0;g;MAP(g;j;LAMBDA(h;j;LET(x;MID(h;SEQUENCE(LEN(h));1);TEXTJOIN(;;VSTACK(x;TAKE(IF(h<>"";x;"");j)))))));""));g;b;LAMBDA(u;c;o;IF(LEN(c)
Excel solution 11 for Encrypt Using Date Cipher, proposed by Julien Lacaze: 
=MAP(A2:A7,B2:B7,LAMBDA(a,b,TEXTJOIN("",TRUE,CHAR(CODE("a")+MOD(CODE(MID(a,SEQUENCE(LEN(a)),1))-CODE("a")+NUMBERVALUE(MID(LEFT(REPT(b,ROUNDUP(LEN(a)/LEN(b),0)),LEN(a)),SEQUENCE(LEN(a)),1)),26)))))
Excel solution 12 for Encrypt Using Date Cipher, proposed by Pieter de Bruijn: 
=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(am,MID(a,SEQUENCE(LEN(a)),1),bm,MID(b,MOD(SEQUENCE(LEN(a),,0),LEN(b))+1,1),x,CODE(am)+bm,
CONCAT(CHAR(x-(26*(x>122)))))))
Excel solution 13 for Encrypt Using Date Cipher, proposed by Guillermo Arroyo: 
=MAP(A2:A7;B2:B7;LAMBDA(a;b;CONCAT(CHAR(MOD(CODE(MID(a;SEQUENCE(LEN(a));1))-97+MID(b&b&b&b;SEQUENCE(LEN(a));1);26)+97))))
Excel solution 14 for Encrypt Using Date Cipher, proposed by Daniel Garzia: 
=MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(l,LEN(x),s,SEQUENCE(l),n,CODE(MID(x,s,1))+MID(LEFT(REPT(y,3),l),s,1),CONCAT(CHAR(IF(n>122,n-26,n))))))
Excel solution 15 for Encrypt Using Date Cipher, proposed by Henriette Hamer: 
=LOWER(TEXTJOIN("";TRUE;CHAR(+CODE(MID(UPPER(A2);SEQUENCE(LEN(A2);1;1;1);1))+MID(B2&B2&B2;SEQUENCE(LEN(A2);1;1;1);1)*1-IF(+CODE(MID(UPPER(A2);SEQUENCE(LEN(A2);1;1;1);1))+MID(B2&B2&B2;SEQUENCE(LEN(A2);1;1;1);1)*1>90;26;0))))
except that it gives the wrong answer for the first row, but I feel that "angpy" might have had to be "gngpy"? (e + 2 = g, not a)

Solving the challenge of Encrypt Using Date Cipher with Excel VBA

Excel VBA solution 1 for Encrypt Using Date Cipher, proposed by Nicolas Micot: 
VBA solution:
Function caesar(ByVal message As String, ByVal key As String) As String
Dim letter As String
Dim num As Integer, dec As Integer, code As Integer
For i = 1 To Len(message)
 num = num + 1
 If num > Len(key) Then num = 1
 letter = Mid(message, i, 1)
 code = Asc(letter)
 dec = Mid(key, num, 1)
 caesar = caesar & Chr(code + dec + IIf(code + dec > Asc("z"), -26, 0))
Next i
End Function

&&&

Leave a Reply