Sat 04 Nov 2017

Tips & Tricks 165 - Convert Weekday Names to Numbers

By |Saturday, November 04th, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

Suppose Cell A2 contains weekday names like Sunday, Monday.....(or Sun, Mon...), then following formula can be used to return the numbers. Sunday will be 1 and Saturday will be 7.

=ROUND(SEARCH(LEFT(A2,2),"SuMoTuWeThFrSa")/2,0)

=MATCH(LEFT(A2,2),{"Su","Mo","Tu","We","Th","Fr","Sa"},0)

If we want to return some other number to weekdays, then formula can be tweaked accordingly. For example, to make Mon = 1 and Sun = 7

=ROUND(SEARCH(LEFT(A2,2),"MoTuWeThFrSaSu")/2,0)

=MATCH(LEFT(A2,2),{"Mo","Tu","We","Th","Fr","Sa","Su"},0)

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 17 Sep 2016

Tips & Tricks 145 - Determine the First Sunday or any other Day given Weeknumber

By |Saturday, September 17th, 2016|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

If you have been given a week number and has been asked to find the first Sunday for that week, you can use following formula

=CEILING(("1JAN"&A1)-14,7)+8+7*(5-1)

Where A1 has the year say A1=2016

5 is the Week Number which you can replace.

For Finding Monday, add 1 in the formula, add 2 for Tuesday and so on.

The above formula assumes that WEEKNUM function has Sunday as the starting day for the week. If you have any other day for the week as the starting day of the week, you will have to customize the above formula as per the need.

 

Sat 16 Jan 2016

Article 34 - Show Date for a Coming or Previous Day (Say Coming Monday, Previous Friday)

By |Saturday, January 16th, 2016|Categories: Articles|Tags: , , , , , , , , , , , , , , , , , |2 Comments

I had already written about one formula where I dealt with coming day formula - Article 3 – Show Date for a Coming Day (e.g. Coming Saturday)

I wanted to revisit this with following things in mind -

1. I wanted to explore various formulas on the above topic. Here, I am giving 2 additional formulas. So, I will be talking about a total of 3 formulas.

2. In Article 3, I talked only about Coming Day not the Previous Day. In this article, I will be talking about formulas for Previous Day also.

(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.