Sun 06 Sep 2015

Solution - Challenge 22 – Another Friday the 13th Challenge – Find Actual Dates themselves

By |Sunday, September 06th, 2015|Categories: Solutions|Tags: , |0 Comments

Below is a possible solution to the challenge - Challenge 22 – Another Friday the 13th Challenge – Find Actual Dates themselves

If A1 and A2 contain the start and end dates, put following formula and drag down -

=IFERROR(AGGREGATE(15,6,(ROW(INDIRECT($A$1&":"&$A$2)))/((TEXT(ROW(INDIRECT($A$1&":"&$A$2)),
"ddd")="Fri")*(TEXT(ROW(INDIRECT($A$1&":"&$A$2)),"dd")="13")),ROWS($1:1)),"")

A workbook illustrating the same can be downloaded from Solution - Challenge 22 – Another Friday the 13th Challenge – Find Actual Dates themselves

 

Mon 10 Aug 2015

Solution - Challenge 20 – Find Number of Friday the 13th between Two Given Dates

By |Monday, August 10th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 20 – Find Number of Friday the 13th between Two Given Dates

The formula for finding this would be -

=SUMPRODUCT((TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")="Fri")*(TEXT(ROW(INDIRECT(A1&":"&A2)),"dd")="13"))

Sat 01 Aug 2015

Challenge 22 - Another Friday the 13th Challenge - Find Actual Dates themselves

By |Saturday, August 01st, 2015|Categories: Challenges|Tags: , , , |1 Comment

Challenge 20 was about finding the number of Friday the 13th between two given dates. This challenge is about actually listing down the Friday the 13th dates themselves.

Suppose, you been given 2 dates.

A1=1/1/2014
A2 = 12/31/2015

The challenge before you is to write a formula which can be dragged down and will list the dates which were Friday the 13th.

Hence, for above date range, the answer should be -

13-Jun-14
13-Feb-15
13-Mar-15
13-Nov-15

You may post your answer in the comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 31-Aug-15.

Sat 11 Jul 2015

Challenge 20 - Find Number of Friday the 13th between Two Given Dates

By |Saturday, July 11th, 2015|Categories: Challenges|Tags: , , , , |1 Comment

I had read the example of this problem in Mike Girvin's book on Array Formulas. So, I am posing this challenge here for my readers.

Suppose, you have two given dates -

A1: 1/1/2013

A2: 12/31/2015

The challenge before you is to write a formula to find the the days which were Friday the 13th. For example, in 2015 following dates were Friday the 13th -

13-Feb-15
13-Mar-15
13-Nov-15

Hence, if date range is 1/1/2015 to 12/31/2015, the answer would be 3.

You may post your answer in Comments section.

Note - Solution to this challenge will be published on after 1 month i.e. on 10-Aug-15.