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

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