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),"")
Author: Vijay A. Verma
Downloads 13 – Template 11 – Invoice Template
The invoice template is specifically designed for Service Invoices in India but this can be used anywhere in the world with little customization. There are two templates, one which populates the amount in words in Rupees and Paise and one which populates in Dollar and Cents. If you need to change these currencies, you can…
Solution – Challenge 59 – Clean the Problem Workbook Data
Below is a possible solution to the problem Challenge 59 – Clean the Problem Workbook Data Formula to convert would be which you need to drag down would be =–SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(8237),""),UNICHAR(8236),"") Data has UNICHAR(8237) and UNICHAR(8236) prefixed and suffixed which need to be replaced by above formula.
Tips & Tricks 157 – Search for Online Templates Greyed Out / Disabled
When we create a new workbook through File > New, we get a box where we can search for online templates. But, at times, it may be greyed out or disabled i.e. you can not type out anything out there. To correct this, you need to enable internet setting for Excel. File > Options (You…
Challenge 60 – Generate a list of 2nd and 4th Saturdays for a Year
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…
Tips & Tricks 156 – Get Workbook's Directory from Formula
If your workbook is located in say C:\Excel\MyDocs, the formula to retrieve the directory for this would be =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2) Note – For this formula to work, you workbook must be saved at least once.
Downloads 12 – Template 10 – A Flexible Amortization Schedule Generator
This flexible Amortization Schedule Generator can be downloaded from Amortization Schedule Template Unprotected template – Amortization-Schedule-Template_Unprotected 1. You can specify Interest Rate not only annually but also weekly, monthly, quarterly and half yearly. Default is Yearly if nothing is specified. 2. You can specify Loan Period in weeks, months, quarters, half years and years. Default…
Tips & Tricks 155 – Gridlines not Visible, Help!!
You may try following one by one and verify your results 1. Update your Printer Driver to latest from internet (Or change your default printer driver to PDF/XPS/One Note) 2. Select the triangle between row 1 and column A to select entire sheet (or CTRL+A, 3 times). Home tab > Go to Paint Bucket and…
Challenge 59 – Clean the Problem Workbook Data
Download the the workbook from the below link. The Challenge before you is to write a formula to clean the data. If you perform any mathematical operation on the data, it will return #VALUE error. You need to clean the data through a formula. Challenge 59 – Problem Workbook The solution to the above problem…
Downloads 11 – Printable & Pin-up Sheet for Excel Functions
You can download this one page pin-up sheet and take the printout and pin up to your desk for quick reference – Excel Functions Quick Reference