Suppose you have two time stamps say A1: 23-Dec-2015 09:15 AM and B1: 29-Dec-2015 02:30 PM and say your working hours are between 09:00 AM to 05:00 PM. You have been tasked to calculate the working hours between these two dates and you need to exclude weekends (here – 26-Dec-15 and 27-Dec-15) and any holiday…
Excel Quiz 27
Tips & Tricks 122 – Always Open a Specific Worksheet when Workbook is Opened
This can only be accomplished through VBA. 1. Save your file as .xlsm 2. ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Double click on ThisWorkbook 5. Put following code in ThisWorkbook (Replace Sheet Name appropriately) Private Sub Workbook_Open() Worksheets("Sheet Name").Activate End Sub
Tips & Tricks 121 – Macro to Protect / Un-protect All or Selective Sheets
There may be scenarios where you want the facility to protect or unprotect all sheets in one go with added option to choose sheets where to perform this operation. Below is the code to perform this. 1. Save your file as .xlsm 2. ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Right…
Solution – Challenge 29 – Reverse (Flip) a Number String
Below is a possible solution to the challenge – Challenge 29 – Reverse (Flip) a Number String You may use below formula to reverse a number string – =SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) *10^ROW(INDIRECT("1:"&LEN(A1)))/10)
Article 31 – Slab Billing – Calculate Income Tax, Electricity (Utility) Bills based on Slabs
You will encounter slab billings in two very common documents – One is Electricity / Utility and another one is Income Tax. If you see your electricity bills, you will notice following type of entries (values are for illustration purposes only, please do not attach any meaning to them) 0 – 50 Units – $1.5…
Challenge 31 – Increment All Digits by 1
This time's challenge is a short one but it may take some time to think through. Suppose, you have a number in cell A1. The challenge is to add 1 in all digits and come with a result. Hence, If A1=28, result would be 39. If A1 = 123, result would be 123+111 = 234…
Excel Quiz 26
Tips & Tricks 120 – Filter by Color Drop Down Menu Greyed Out Problem
Sometimes, you find that though your are having colored cells but Filter by Color is greyed out i.e. it can not be selected. Following are the possible causes – 1. Your sheets may be grouped. If they are grouped, you will notice word "Group" in the title bar of your Excel workbook. Another way to…
Tips & Tricks 119 – Numerology Sum of the Digits aka Sum the Digits till the result is a single digit
In Numerology, it is often a task to add the digits till the result is a single digit. For example, 74 = 7 + 4 = 11 = 1 + 1 = 2 23 = 2 + 3 = 5 78 = 7 + 8 = 15 = 1 + 5 = 6 1234567 =…