Below is a possible solution to the Challenge 63 - Convert to Date Format
Put following formula and drag down
SUBSTITUTE(A1,",","")," ","*",2),"*",", "))
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
The solution to the above challenge will be published after a month i.e. on 10-Jul-17.
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
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.
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