Wed 16 Jan 2019

## Solution - Challenge 68 - Need a Data Validation Formula

By |Wednesday, January 16th, 2019|Categories: Solutions|Tags: |0 Comments

Below is a proposed solution to the Challenge 68 - Need a Data Validation Formula

=IFERROR(IF(ISNUMBER(SEARCH("/",A2)),IF(--RIGHT(A2,4)=2018,AND(--LEFT(A2,2)>=1,
--LEFT(A2,2)<=12),IF(AND(--RIGHT(A2,4)<=2021,
--RIGHT(A2,4)>=2019),AND(--LEFT(A2,2)>=1,--LEFT(A2,2)<=4)))),FALSE)

Sun 16 Dec 2018

## Challenge 68 - Need a Data Validation Formula

By |Sunday, December 16th, 2018|Categories: Challenges|Tags: |1 Comment

Need a data validation formula for following assuming you are putting data validation in cell A2.

The user should be able to enter a date in following format

1. MM/YYYY for the year 2018
2. QQ/YYYY for the years 2019 to 2021

The solution to above challenge will be published after a month i.e. on 16-Jan-18.

Sat 16 Apr 2016

## Article 38 - 10 Features I would like to see in Excel

By |Saturday, April 16th, 2016|Categories: Articles||0 Comments

Like all of you, I am dependent upon day to day work in Excel. Excel is a software which is like ocean, unlimited in bounds. It has almost all features but it doesn't have few which everybody cherishes. All of us want something or the other. Below is a list of features which I would like Excel to have.

### 1. Making Volatile Functions Non-volatile on the basis of a Parameter

We all like TODAY(), NOW(), RAND() and RANDBETWEEN() (there are other volatile functions also). They are very useful but suffer a fatal flaw, if something gets recalculated, they always get recalculated also. Hence, if I am using TODAY() to derive today's day and if I open the sheet tomorrow, TODAY() will change tomorrow's date. I wish that all volatile functions should carry a parameter so that they should not recalculated when the sheet is recalculated. For example, we should have something like TODAY(N), NOW(N),RAND(N) and RANDBETWEEN(N) where N
(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||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

Sat 24 May 2014

## Tips & Tricks 4 - Allow Entry of Date for a Particular Day only

By |Saturday, May 24th, 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. Let's assume that we want users to enter only those dates which are Wednesdays. Put following formula in

=WEEKDAY(A1)=4

4 is for Wednesdayday. 1 is for Sunday and 7 is for Saturday other values lying in between.