Sat 25 Jun 2016

## Excel Quiz 41 - Excel Specifications and Limits Quiz – Part II

By |Saturday, June 25th, 2016|Categories: Quizzes||0 Comments

## Excel Quiz 41

2nd quiz on Excel Specifications and Limits

Sat 25 Jun 2016

## Downloads 04 - Template 04 - A Flexible Agile Burnup Chart

Download the template - Template 04 - Agile Burnup Chart Template v1.2

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 (stories etc.) to reach the goal.

Both have their own pros and cons and both have their own utilities. In my view, in Agile Project Management, both should be used while staying away from trying to prove the superiority of one over the other.

In this template, I am not constraining the look and feel to only one type of Burnup Chart rather I am giving 4 different looks and feels. You can use which one do you want to use.
(more…)

Fri 24 Jun 2016

## Tips & Tricks 139 - Convert from Julian Dates to Excel (Gregorian) Dates

By |Friday, June 24th, 2016|Categories: Tips and Tricks|Tags: , , , |0 Comments

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 into 20th Century)

21st Century
=DATE(20&LEFT(A1,2),1,RIGHT(A1,3))

20th Century
=DATE(19&LEFT(A1,2),1,RIGHT(A1,3))

Note - 19 or 20 can be replaced with some IF condition to put in right 19 or 20 depending upon the year. For example, year 82 is more likely to be in 20th century where year 15 is more likely to be in 21st century.

Tue 21 Jun 2016

## Solution - Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

By |Tuesday, June 21st, 2016|Categories: Solutions||0 Comments

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 contain a maximum of 20 projects and there are 7 days in a week and you can have a maximum of 20*7=140 rows)

Sat 18 Jun 2016

## Challenge 45 - Number of Days Passed in a Quarter

By |Saturday, June 18th, 2016|Categories: Challenges|Tags: , , , , , , |1 Comment

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 days have passed in Q1. Hence, answer is 71.

If A1 has 15-Apr-16, then 14 days have passed in Q2. Hence, answer is 14.

If A1 has 28-Aug-16, then, 31 days in Jul and 27 days in Aug = 31+27 = 58 days have passed in Q3. Hence, answer is 58.

You need to give the formula to find the above.

The solution to above challenge will be published after a month i.e. on 18-Jul-16.

Sat 18 Jun 2016

## Article 40 - Order of Operations in Formula

By |Saturday, June 18th, 2016|Categories: Articles||0 Comments

B – Brackets (Parentheses)
O – Orders (Powers/Exponents)
D – Division
M – Multiplication
S – Subtraction (more…)

Sat 11 Jun 2016

## Excel Quiz 40 - Date Functions

By |Saturday, June 11th, 2016|Categories: Quizzes||0 Comments

## Excel Quiz 40

A quiz on date functions of Excel

Fri 10 Jun 2016

## Tips & Tricks 138 - Convert from Excel Date (Gregorian Date) to Julian Date

By |Friday, June 10th, 2016|Categories: Tips and Tricks|Tags: , , , |1 Comment

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 - 16092

Q. What formulas to use to convert Excel Dates to Julian Dates?
A. For 7 Digits, use following formula
=TEXT(A1,"yyyy")&TEXT(A1-("1JAN"&YEAR(A1))+1,"000")

For 5 Digits, use following formula
=TEXT(A1,"yy")&TEXT(A1-("1JAN"&YEAR(A1))+1,"000")

Tue 07 Jun 2016

## Solution - Challenge 42 – Split a Sentence Into Words in Different Cells

By |Tuesday, June 07th, 2016|Categories: Solutions|Tags: , , , , , , , , |0 Comments

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 - Challenge 42 – Split a Sentence Into Words in Different Cells

Sat 04 Jun 2016

## Challenge 44 - Hyperlink to a cell in another worksheet by specific text (not cell number)?

By |Saturday, June 04th, 2016|Categories: Challenges|Tags: , , , , , , , |1 Comment

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 the word was Pretty in A1.

You can download the worksheet related to this challenge from Challenge 44 - Hyperlink to a cell in another worksheet by specific text (not cell number)

The solution to above challenge will be published after a month i.e. on 04-Jul-16.