Tips & Tricks 80 – Last Working Day of the Month If a Date is Given
If A1 holds a date, the formula for calculating last Working Day of the month would be =WORKDAY(EOMONTH(A1,0)+1,-1) The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula – =WORKDAY.INTL(EOMONTH(A1,0)+1,-1,"0000110") Where 0000110 is a 7 character string, 1 represents…
Tips & Tricks 79 – How to Know if a Year is a Leap Year
Let's say that A1 contains the year. To know whether it is a Leap Year or not, use following formula – =MONTH(DATE(A1,2,29))=2 TRUE means that it is Leap Year and FALSE means that this is not a Leap Year.
Tips & Tricks 78 – Number of Days in a Month
Suppose, you have been given a date say 15-Nov-14 and you have to determine how many days this particular month contains. The formula which you need to use in the above case would be =DAY(EOMONTH(A1,0)) Explanation – EOMONTH(A1,0) gives the last date of the month and DAY function extract that particular Day from the last…
Challenge 9 – Get the Customer Spending Maximum Amount for a given Month and Starting Alphabet
Given the below table, the challenge before you is to – Work out a formula for Customer spending Maximum amount and his Spend (Cells H4 and H5) for a given Month (H2) and starting alphabet (H3). Cell H2 has the month in numbers 1 to 12 and H3 has starting alphabet. If H2 is blank,…
Article 11 – How to Encrypt Excel Workbook
Note – The article is for Excel 2010 / 2013 versions and doesn't cover any version prior to Excel 2010. Excel 2010 / 2013 uses default 128 bit AES encryption which is considered highly secured. If you are interested in knowing all things about Excel security, I will recommend following MS article – http://technet.microsoft.com/en-us/library/dn194021%28v=office.15%29.aspx —-…
Tips & Tricks 77 – Inserting picture in the comments box
The below discussion is for Excel 2013. If a cell is selected and it has comments, the comments will be displayed. Sometimes, there may be a need to display picture when you select a cell. I answered this question on Microsoft Community Forum and I have decided to reproduce it here. 1. Right click the…
Excel Quiz 6
Tips & Tricks 76 – Whenever I Refresh a Pivot, its Column Width Changes
You can stop Pivot from doing this behaviour. Take following steps – 1. Right Click on Pivot Table > PivotTable Options 2. Uncheck Autofit column widths on update.
Article 10 – Intersection Operator in Excel
Today, I am going to talk about a nifty but not so well known feature of Excel. This is about Intersection Operator in Excel. Before, I go deep in Intersection Operator, I would like to talk about Reference Operators in Excel. There are 3 Reference Operators – 1. Range Operator (represented by Colon) – It…