Sat 03 Oct 2015

Tips & Tricks 112 - Convert a Number into Years and Months

By |Saturday, October 03rd, 2015|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

Suppose, you have been given a number into cell A1 say 26 and you want to display it as 2 Years and 4 Months, you can use following formula -

=INT(A1/12)&" Years and "&MOD(A1,12)&" Months"

Now, an user can become more demanding and he can say that if month is less than 12, then Years should not be displayed. For example, he might say that 8 should be converted to 8 Months and it should not be shown as 0 Years and 8 Months.

In this case, the formula would be -

=IF(INT(A1/12)>0,INT(A1/12)&" Years and ","")&MOD(A1,12)&" Months"

Now 8 will be displayed as 8 Months only not as 0 Years and 8 Months.

Now, user can ask more. He can say when I give 12, it displays as 1 Years and 0 Months and he simply wants to see 1 Years only. And for 36, he wants to see only 3 Years not 3 Years 0 Months. In this case, formula will have to be tweaked more. Now, the formula becomes -

=IF(INT(A1/12)>0,INT(A1/12)&" Years ","")&IF(MOD(A1,12)=0,"",MOD(A1,12)&" Months")

Now an user can come and can ask for one last thing. He can say that if this is 1 Year or 1 Month, it should not be displayed as Years or Months as 1 is not plural. Hence, 25 should be displayed as 2 Years and 1 Month not as 2 Years and 1 Months. Hence, 18 should not be displayed as 1 Years and 6 Months but as 1 Year and 6 Months. Similarly 13 should be displayed as 1 Year and 1 Month not as 1 Years and 1 Months.

=IF(INT(A1/12)>0,INT(A1/12)&" Year"&IF(INT(A1/12)>1,"s","")&" and ","")&MOD(A1,12)&" Month"&IF(MOD(A1,12)>1,"s","")

Sat 29 Aug 2015

Article 24 - Convert a Number to a Month Name

By |Saturday, August 29th, 2015|Categories: Articles|Tags: , , , , , , |2 Comments

Use below formula to generate named 3 lettered month like Jan, Feb....Dec

=TEXT(A1*30,"mmm")

Replace "mmm" with "mmmm" to generate full name of the month like January, February....December in any of the formulas in this post.

(more…)

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.

Sat 27 Jun 2015

Challenge 19 - Make Comparative Horizontal Bar Graph

By |Saturday, June 27th, 2015|Categories: Challenges|Tags: , , , , , , , , , , |2 Comments

---- The worksheet for this challenge can be downloaded from Literacy Rates Graph ----

Suppose, you have been given data for two categories as given below. The challenge before you is to make a chart like below.

Literacy Graph Data

Literacy Graph Chart

You can choose to post your answer in comments section.

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