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 –…
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…