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 –…
Author: Vijay A. Verma
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…
Excel Quiz 32
Downloads 03 – Template 03 – A Flexible Agile Burndown Chart
The template can be download from – Template 03 – Agile Burndown Chart Template Unprotected version of template – Template 03 – Agile Burndown Chart Template_Unprotected I decided to create a template for a Burndown Chart in Excel which is very highly used in Agile Project Management. Next download template will be about Burnup Chart…