Sat 01 Jul 2017

Tips & Tricks 161 - When is Thanksgiving Day in a Year

By |Saturday, July 01st, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

Last time, we discussed about finding Labor Day in a given year. This time, it is is about Thanksgiving Day in a year. Thanksgiving day is 4th Thursday in a November.

Hence, earliest possible day when 4th Thursday can happen is on 22-Nov.

(more…)

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…)