In many scenarios, you may be using a symbol very often. For example, new Indian Currency Symbol ₹. Every time, you need to insert symbol which is many steps process and introduces inefficiency. You can use following method to assign a short-cut key to a symbol. (Below example is actually not a short cut key but…
Category: Tips and Tricks
Tips & Tricks 5 – Allow Entry of Dates if they are not Weekends
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 4. Customize your Input and Error Message as per your requirements.
Tips & Tricks 4 – Allow Entry of Date for a Particular Day only
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…
Tips & Tricks 3 – Add Year to or Subtract Year from a Given Date
In many business problems, you might encounter situations where you will need to add or subtract years from a given date. Let's say A1 contains Date and B1 contains numbers of years. If you want to add Years to a given date, formulas would be – =EDATE(A1,12*B1) =DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1)) If you want to subtract Years from…
Tips & Tricks 2 – Add Month to or Subtract Month from a Given Date
Very often, you will have business problems where you have to add or subtract month from a given date. One scenario is calculation for EMI Date. Say, you have a date of 10/22/14 (MM/DD/YY) in A1 and you want to add number of months which is contained in Cell B1. The formula in this case…
Tips & Tricks 1 – A List is Unique or Not (Whether it has duplicates)
Assuming, your list is in A1 to A1000. Use following formula to know if list is unique. =MAX(FREQUENCY(A1:A1000,A1:A1000)) =MAX(INDEX(COUNTIF(A1:A1000,A1:A1000),,)) If answer is 1, then it is Unique. If answer is more than 1, it is not unique.