Sun 03 Sep 2017

Tips & Tricks 162 - Convert a Month Name to Month Number

By |Sunday, September 03rd, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , |0 Comments

Suppose, you have text denoting month in cell A1. Let's say A1 = "Sep" or A1="September", then you can use following formula to convert this to month number

=MONTH(1&A1)

=--TEXT(1&A1,"m")

In case, cell A1 contains  the partial month name say "Septe", then in place of A1 in the above formulas, you can write LEFT(A1,3).

Sat 01 Jul 2017

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

By |Saturday, July 01st, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |1 Comment

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 Jun 2017

Tips & Tricks 160 - When is Labor Day in a Given Year

By |Saturday, June 03rd, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

While 1st May is celebrated as Labour Day in most of the countries but in USA/Canada, it is celebrated on 1st Monday of September.

Suppose the year is given in cell A1, you can use following formula to calculate the date for Labor Day

=CEILING(DATE(A1,9,1)-2,7)+2

Note - This utilizes the knowledge gained in Article 34 - Show Date for a Coming or Previous Day (Say Coming Monday, Previous Friday)

1

Sat 23 Apr 2016

Downloads 02 - Template 02 - A Highly Customizable Perpetual Yearly Calendar Template in Excel

By |Saturday, April 23rd, 2016|Categories: Downloads|Tags: , , , , , , , , , , , , , |0 Comments

Download link for this Calendar Template 02 - Calendar Template Yearly

The second template in the series is a calendar. Just download it once and use every year. Following are the features -

1. The calendar is perpetual in nature. It supports all the years from 1900 to 9999. You just need to change the year every year or you can leave the year field blank. In that case, it would take the current year as the year.
2. It features a list where you can put important dates and those dates will automatically be shaded.
3. You can define your weekends. It can be one weekend or two weekends.
4. You can define your start day of the week. It can be any day between Mon to Sun.
(more…)