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 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 25 Feb 2017

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

By |Saturday, February 25th, 2017|Categories: Challenges|Tags: , , , , , , , |1 Comment

In India, 2nd and 4th Saturdays are very important days as on these days, the banks remain closed. Hence, banks in India are closed on all Sundays and 2nd and 4th Saturdays apart from their holiday calendar.

To calculate Networkdays for banking industry in India needs that the list of 2nd and 4th Saturdays needs to be generated. You know the syntax of NETWORKDAYS -

NETWORKDAYS.INTL(Start_Date, End_Date, [Weekend],[Holidays])

In weekend parameter, we can specify Sunday as weekend and in Holidays list, we can put the holidays and also the 2nd and 4th Saturdays.

Now, the challenge before you is to write a formula which takes Year as Input from A1 and which when dragged down produces the 2nd and 4th Saturdays.

1

Note - The solution to above problem will be published after a month i.e. on 25-Mar-17.

Sat 26 Sep 2015

Article 26 - Converting Amount into Words (Indian Currency - Rupees and Paise)

By |Saturday, September 26th, 2015|Categories: Articles, VBA|Tags: , , , , , , , |6 Comments

Below is the VBA function where you can convert a given amount into Words on the basis of Indian Currency. India utilizes a system which is based on Hundred, Thousand, Lakh.....unlike English system which is based on Hundred, Million, Billion..(Indian Numbering System)

Below are numerical equivalent of Indian words

Lakh - 1,00,000
Crore - 1,00,00,000
Arab - 1,00,00,00,000 (more…)