Mon 10 Jul 2017

Solution - Challenge 63 - Convert to Date Format

By |Monday, July 10th, 2017|Categories: Solutions|Tags: , , , , , , , , , , , |0 Comments

Below is a possible solution to the Challenge 63 - Convert to Date Format

Put following formula and drag down

=IFERROR(--SUBSTITUTE(A1,",",""),--SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1,",","")," ","*",2),"*",", "))

 

Sat 10 Jun 2017

Challenge 63 - Convert to Date Format

By |Saturday, June 10th, 2017|Categories: Challenges|Tags: , , , , , , , , , , , |1 Comment

This time, you have been given a file containing Text dates into various formats. The challenge before you is to write a single formula to convert them into an appropriate date format.

The challenge file can be downloaded from Challenge 63 - Convert to Date Format

1

The solution to the above challenge will be published after a month i.e. on 10-Jul-17.

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 21 May 2016

Article 39 - Sorting in Excel

By |Saturday, May 21st, 2016|Categories: Articles|Tags: , , , , , , , , , , |0 Comments

First thing first – Excel doesn’t sort as per ASCII character codes which we generally expect. It has its own sorting logic which is detailed below for Ascending sort (reverse the same for Descending sort).

The order followed in an Ascending sort is Number > Text > Alphanumeric text > Logical values > Error values > Blanks

(more…)