Below is a possible solution to the challenge – Challenge 20 – Find Number of Friday the 13th between Two Given Dates The formula for finding this would be – =SUMPRODUCT((TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")="Fri")*(TEXT(ROW(INDIRECT(A1&":"&A2)),"dd")="13"))
Tips & Tricks 104 – Hide a Sheet Securely
You want to hide a sheet so that nobody else can open that. This may arise in some situations like you have a dump of employee data which has sensitive information like salary, last rating, age etc. You want to keep a copy of this in your workbook but you want to refer to only…
Tips & Tricks 103 – I need to fill in A-Z or a-z very often. What to do
1. Put =CHAR(96+ROW()) in cell A1. 2. Drag down till A26 to generate a to z. 3. Put =CHAR(64+ROW()) in cell B1. 4. Drag down till B26 to generate A to Z. 5. File > Options > Advanced > Go down till you find Edit Custom Lists (You have to go down till bottom) and…
Excel Quiz 18
Tips & Tricks 102 – How to Find all Links in an Excel Workbook
Rogue links troubling your workbooks. You have visually inspected them and still you have no clue where to find them, then read on… Approach 1 – Easiest Approach Bill Manville offers a free Add-in which finds all links given in your Excel. The utility is a very popular one and it should be must have…
Article 21 – Clipboard Error in Excel – We couldn't free up space on the Clipboard
Many times when working in the Excel following errors may be encountered – > We couldn't free up space on the Clipboard. Another program might be using it right now > The clipboard cannot be emptied. Another program might be using the clipboard
Article 20 – Miscellaneous Errors in Excel – Sending Command to Excel, Greyed Out Options (Not Applicable to Windows 10)
Over a period of time, Excel has become a complex piece of software. Many new features have been introduced and on top of that increasing requirement for security has introduced further complexity for Excel developers. Many times, users report many different kind of errors while using Excel. Following are the common ones – 1. An…
Article 19 – How to Check if a cell contains a date
Checking for dates in Excel can be a very interesting problem as Excel stores dates as numbers. For example, 4-Jul-15 is stored as 42189. To elaborate further on this, 1-Jan-1900 is treated as 1. The difference between a date and 1-Jan-1900 +1 is the serial number of that date. hence, in case of 4-Jul-15, it…
Challenge 22 – Another Friday the 13th Challenge – Find Actual Dates themselves
Challenge 20 was about finding the number of Friday the 13th between two given dates. This challenge is about actually listing down the Friday the 13th dates themselves. Suppose, you been given 2 dates. A1=1/1/2014 A2 = 12/31/2015 The challenge before you is to write a formula which can be dragged down and will list…
Solution – Challenge 19 – Make Comparative Horizontal Bar Graph
Below is a proposed solution to the challenge – Challenge 19 – Make Comparative Horizontal Bar Graph 1. Download the Excel sheet from the challenge. 2. Put "F" without quotes in D1 and put following formula in D2 and drag down =-C2 3. Now select A1:B9, hold CTRL and select D1:D9. 4. Insert the bar…