Below is a possible solution to the challenge – Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)? Enter the below formula in B1 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,…
Challenge 46 – Compute Numerological Sum for a Name
I had posted Tips & Tricks 119 – Numerology Sum of the Digits aka Sum the Digits till the result is a single digit. In this, I had explored how to add a number and arrive at a single digit. For example, if you have to add 8 + 7 the answer would be 15….
Downloads 04 – Template 04 – A Flexible Agile Burnup Chart
Download the template – Template 04 – Agile Burnup Chart Template v1.2 Download Password free version of this template – Template-04-Agile-Burnup-Chart-Template-v1.3_No Password When I posted the Burndown template, I promised that next template would be Burnup template. Unlike Burndown which shows remaining effort (stories etc.) to reach the goal, Burnup chart shows the effort spent…
Tips & Tricks 139 – Convert from Julian Dates to Excel (Gregorian) Dates
In Tips and Tricks 138, we covered conversion from Excel Dates to Julian Dates. Here, we want to look at reverse. For 7 Digits Julian Dates, following formula should be used =DATE(LEFT(A1,4),1,RIGHT(A1,3)) For 5 Digits Julian Dates, following formula should be used depending upon which century (Note – Julian dates are most likely to fall…
Solution – Challenge 43 – Consolidate Daily Sheets into a Weekly Sheet
Below is a possible solution to the challenge – Challenge 43 – Consolidate Daily Sheets into a Weekly Sheet 1. Create a Sheet named Project which can hold all the projects. In that sheet, enter following formula in A2 and copy down till row 141 in columns A, B and C (as every sheet can…
Challenge 45 – Number of Days Passed in a Quarter
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 –…