This time the challenge is – If a date is given, what would be the formula to find the number of days passed in a quarter. If A1 has the value 12-Mar-16, then 31 days in Jan, 29 days in Feb (2016 is a leap year) and 11 days in Mar = 31+29+11 = 71…
Article 40 – Order of Operations in Formula
Follow the BODMAS rule B – Brackets (Parentheses) O – Orders (Powers/Exponents) D – Division M – Multiplication A – Addition S – Subtraction
Tips & Tricks 138 – Convert from Excel Date (Gregorian Date) to Julian Date
Q. First what is a Julian Date? A. A Julian date has either 7 digits or 5 digits date and these are generally used in old IT legacy systems. 7 Digits – YYYYDDD – 2016092 (This is 1-Apr-2016. 92 means that this is 92nd day from 1-Jan in that year) 5 Digits – YYDDD –…
Solution – Challenge 42 – Split a Sentence Into Words in Different Cells
Below is a possible solution to the challenge – Challenge 42 – Split a Sentence Into Words in Different Cells Enter following formula in B1 and drag to the right and down. =IFERROR(MID($A1,FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:A))), FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:B)))-1- FIND("@",SUBSTITUTE(" "&$A1&" "," ","@",COLUMNS($A:A)))),"") The workbook containing this solution can be downloaded from Solution –…
Excel Quiz 33
Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)?
You have a grid of English words in Sheet2. In A1 of Sheet1, you have been given a word. You need to create a Hyperlink through formula in B1 of Sheet1. The Hyperlink which says "Take to the Word – Discover" if the word was Discover in A1. "Take me the Word – Pretty" if…
Tips & Tricks 137 – VBA – Delete Blank Rows for a Range Quickly (without Looping)
Suppose, you have data like this and you want to delete rows 3,4,6,8,11 & 12 as they are blanks. You need not loop through all cells but you can perform this operation is one shot. You can use below code for the same On Error Resume Next Range("A1:A13").SpecialCells(xlBlanks).EntireRow.Delete On Error GoTo 0
Solution – Challenge 41 – Sum the Maximum Number where duplicates Exist
Below is a possible solution to the challenge Challenge 41 – Sum the Maximum Number where duplicates Exist Enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put…
Challenge 43 – Consolidate Daily Sheets into a Weekly Sheet
This time, challenge is going to be tougher. An user receives a daily sheet which has got project status and you need to prepare a consolidated worksheet for the week everyday. 1. Your sheets are named Day1, Day2…..Day7. 2. One sheet can have a maximum of 20 entries. 3. The entries may not be same…
Article 39 – Sorting in Excel
First thing first – Excel doesn’t sort as per ASCII character codes which we generally expect. It has its own sorting logic which is detailed below for Ascending sort (reverse the same for Descending sort). The order followed in an Ascending sort is Number > Text > Alphanumeric text > Logical values > Error values…