Tue 08 Nov 2016

Solution - Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter

By |Tuesday, November 08th, 2016|Categories: Solutions|Tags: , , , , , , , , , , , |0 Comments

Below is a possible solution to the Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter.

Put following formula and drag down

=IFERROR(CHAR(IF(CODE(LOWER(G2))-$B$3<97,CODE(G2)-$B$3+26,CODE(G2)-$B$3)),"")

The Excel file containing the solution can be downloaded from Solution - Challenge 53 - Decryption - Caesar's Shift Cipher

Sat 08 Oct 2016

Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter

By |Saturday, October 08th, 2016|Categories: Challenges|Tags: , , , , , , , , , , , |2 Comments

We published a cryptography challenge Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher. The purpose of the challenge was to create an Excel based Encrypter.

This time, we need to create the de-crypter for the same. The Excel file related to this challenge can be downloaded from Challenge 53 - Decryption - Caesar's Shift Cipher

(more…)

Fri 24 Jun 2016

Tips & Tricks 139 - Convert from Julian Dates to Excel (Gregorian) Dates

By |Friday, June 24th, 2016|Categories: Tips and Tricks|Tags: , , , |0 Comments

In Tips and Tricks 138, we covered conversion from Excel Dates to Julian Dates. Here, we want to look at reverse.

For 7 Digits Julian Dates, following formula should be used

=DATE(LEFT(A1,4),1,RIGHT(A1,3))

For 5 Digits Julian Dates, following formula should be used depending upon which century (Note - Julian dates are most likely to fall into 20th Century)

21st Century
=DATE(20&LEFT(A1,2),1,RIGHT(A1,3))

20th Century
=DATE(19&LEFT(A1,2),1,RIGHT(A1,3))

Note - 19 or 20 can be replaced with some IF condition to put in right 19 or 20 depending upon the year. For example, year 82 is more likely to be in 20th century where year 15 is more likely to be in 21st century.

Fri 10 Jun 2016

Tips & Tricks 138 - Convert from Excel Date (Gregorian Date) to Julian Date

By |Friday, June 10th, 2016|Categories: Tips and Tricks|Tags: , , , |1 Comment

Q. First what is a Julian Date?
A. A Julian date has either 7 digits or 5 digits date and these are generally used in old IT legacy systems.
7 Digits - YYYYDDD - 2016092 (This is 1-Apr-2016. 92 means that this is 92nd day from 1-Jan in that year)
5 Digits - YYDDD - 16092

Q. What formulas to use to convert Excel Dates to Julian Dates?
A. For 7 Digits, use following formula
=TEXT(A1,"yyyy")&TEXT(A1-("1JAN"&YEAR(A1))+1,"000")

For 5 Digits, use following formula
=TEXT(A1,"yy")&TEXT(A1-("1JAN"&YEAR(A1))+1,"000")

 

Tue 26 Apr 2016

Solution - Challenge 39 – Cryptography Challenge 1 – Caesar’s Shift Cipher

By |Tuesday, April 26th, 2016|Categories: Solutions|Tags: , , , , , , , , , , , |1 Comment

Below is a possible solution to the challenge Challenge 39 – Cryptography Challenge 1 – Caesar’s Shift Cipher.

Put below formula in G2 and drag down -

=IFERROR(CHAR(IF(CODE(LOWER(D2))+$B$3>122,CODE(D2)+$B$3-26,
CODE(D2)+$B$3)),"")

The workbook containing the solution can be downloaded from Solution - Challenge 39 - Caesar's Shift Cipher.

Sat 14 Mar 2015

Tips & Tricks 82 – Date for Nth Day of the Year

By |Saturday, March 14th, 2015|Categories: Tips and Tricks|Tags: , , , |0 Comments

Suppose A1 contains the Year and you are asked to find 69th day of the year which is contained in A2. Then formula for finding Nth day of the year would be

=DATE(A1,1,1)+A2-1