Sat 25 Mar 2017

Solution - Challenge 60 - Generate a list of 2nd and 4th Saturdays for a Year

By |Saturday, March 25th, 2017|Categories: Solutions|Tags: , , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 60 - Generate a list of 2nd and 4th Saturdays for a Year

Drag the below formula down

=IF(ROWS($1:1)<25,FLOOR(DATE($A$1,ROUNDUP(ROWS($1:1)/2,0),
2*(MOD(ROWS($1:1)-1,2)+1)*7),7),"")

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

Sat 12 Jul 2014

Article 3 - Show Date for a Coming Day (e.g. Coming Saturday)

By |Saturday, July 12th, 2014|Categories: Articles|Tags: , , , , , , , , , , |1 Comment

There are many circumstances in life where we want to determine the coming day. We are always eager to wait for Saturday. Hence, we want to show the coming Saturday date. We want to go to a blockbuster movie on Wednesday, hence we want coming Wednesday date. All these are fairly easy by Windows Date / Time Calendar or Calendars on mobile  or through other calendars in day to day life. Excel not needed. But in business scenarios, Excel is the tool which you would most likely use.

Now, consider Excel and business scenarios where you would really need the help of this article. Your shift starts on Monday and you want to display date for coming Monday.

Also, suppose you opened your Excel sheet on Monday itself. Now, you are in a dilemma whether to show today's date itself or next Monday's date. For example, you opened your sheet on 26-May-14 and you need to decide whether to show 26-May-14 as coming Monday's date or 2-Jun-14 as coming Monday's date.

I had responded to a question on Microsoft Excel Community Forum and many persons had posted the response. I particularly found the answer of Ashish Mathur very interesting. That discussion can be read here. There are many ways to achieve the objectives  but this particular formula, I found to be very elegant.

(more…)

Sat 31 May 2014

Tips & Tricks 5 - Allow Entry of Dates if they are not Weekends

By |Saturday, May 31st, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

1. Select the Cell > Data Tab > Data Validation > Data Validation

2. Under Settings Tab, in Allow, select Custom

3. Put following formula

=WEEKDAY(A1,16)>2

1

4. Customize your Input and Error Message as per your requirements.