This time, you have been given a file containing Text dates into various formats. The challenge before you is to write a single formula to convert them into an appropriate date format. The challenge file can be downloaded from Challenge 63 – Convert to Date Format The solution to the above challenge will be published…
Tips & Tricks 160 – When is Labor Day in a Given Year
While 1st May is celebrated as Labour Day in most of the countries but in USA/Canada, it is celebrated on 1st Monday of September. Suppose the year is given in cell A1, you can use following formula to calculate the date for Labor Day =CEILING(DATE(A1,9,1)-2,7)+2 Note – This utilizes the knowledge gained in Article 34…
Article 45 – How to know if a Cell contains Time
We talked about Article 19 – How to Check if a cell contains a date. Now, in this article, I will talk about checking the cell for time. The way Excel stores dates as numbers, in the same way it stores time as numbers only. Only change is in range. The numbers get stored between…
Challenge 62 – Produce the Sum for Merged Cells Headers
Download the workbook related to this challenge – Sum for Merged Cells This time challenge is to produce the sum where header is merged cells. For the layout like below – You need to write a single formula which can be dragged right and down to generate the sum for below table The solution to…
Downloads 15 – Excel Formulas Bible
This is one single document which contains close to 100 formulas dealing with various situations. Useful for Intermediate and Advanced users. Download it from Excel – Formulas Bible
Solution – Challenge 61 – Generate Multiplication Table
Below is a possible solution to the challenge – Challenge 61 – Generate Multiplication Table Put following formula and drag right and down – =ROWS($1:1)*COLUMNS($A:A)
Excel Quiz 37
Tips & Tricks 159 – Prompted to "Save File" even if I haven't changed anything
When you open a workbook and you change something in the workbook and you close the workbook, it will ask you to save the workbook. But sometimes, even if you have not changed anything, still the workbook asks to be saved even if you haven't changed. Point 1 – Microsoft has provided the answer to…
Downloads 14 – Excel Shortcuts Bible
This is a mammoth work. Contains all short cut keys published by Microsoft and also all ALT keys which are not published by Microsoft. Download it from Excel – Shortcuts Bible
Tips & Tricks 158 – Overcoming column_index_number problem in VLOOKUP when a column is inserted / deleted
One of the negative points which gets attributed to VLOOKUP is that whenever a column is added / inserted within the range of VLOOKUP, the column index number doesn't change. Hence, it gives wrong result. Let's consider below dataset and for a given Emp ID, I need gender of that person. Hence, for Emp ID,…