Sat 18 Nov 2017

Tips & Tricks 166 - Convert a Number to Weekday Name

By |Saturday, November 18th, 2017|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

Suppose you want to return 1 = Sunday, 2 = Monday…..7 = Saturday

=TEXT(A1&"Jan2017","dddd")

To show only 3 characters of the Weekday Name

=TEXT(A1&"Jan2017","ddd")

You can add a number to A1 if you want to show some other Weekday Name

Say, if you want to show 1 = Monday, 2 = Tuesday…….7 = Sunday, just add 1 to A1

=TEXT(A1+1&"Jan2017","dddd")

Say, if you want to show 1 = Friday, 2 = Saturday…….7 = Thursday, just add 5 to A1

=TEXT(A1+5&"Jan2017","dddd")

Sun 03 Sep 2017

Tips & Tricks 162 - Convert a Month Name to Month Number

By |Sunday, September 03rd, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , |0 Comments

Suppose, you have text denoting month in cell A1. Let's say A1 = "Sep" or A1="September", then you can use following formula to convert this to month number

=MONTH(1&A1)

=--TEXT(1&A1,"m")

In case, cell A1 contains  the partial month name say "Septe", then in place of A1 in the above formulas, you can write LEFT(A1,3).

Sat 25 Mar 2017

Solution - Challenge 60 - Generate a list of 2nd and 4th Saturdays for a Year

By |Saturday, March 25th, 2017|Categories: Solutions|Tags: , , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 60 - Generate a list of 2nd and 4th Saturdays for a Year

Drag the below formula down

=IF(ROWS($1:1)<25,FLOOR(DATE($A$1,ROUNDUP(ROWS($1:1)/2,0),
2*(MOD(ROWS($1:1)-1,2)+1)*7),7),"")

Mon 18 Jul 2016

Solution - Challenge 45 – Number of Days Passed in a Quarter

By |Monday, July 18th, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 45 - Number of Days Passed in a Quarter

The formula to calculate number of days passed in a quarter is

=A1-DATE(YEAR(A1),(ROUNDUP(MONTH(A1)/3,0)-1)*3+1,1)

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.

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")

 

Sat 19 Mar 2016

Tips & Tricks 132 - Financial Year Formula (e.g. 2015-16 or FY16)

By |Saturday, March 19th, 2016|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

A good number of countries don't follow calendar year as the financial year. For example, India's financial year start is 1-Apr and finishes on 31-Mar. Hence, currently (20-Feb-16), the financial year is 2015-16 (It is also written as FY16). On 1-Apr-16, it will become 2016-17 (It is also written as FY17).

Now if a date is given, then following formula can be used to derive 2015-16 kind of result.

=YEAR(A1)-(MONTH(A1)<=3)&"-"&YEAR(A1)+(MONTH(A1)>3)

To generate FY16 kind of result, following formula can be used

="FY"&RIGHT(YEAR(A1)+(MONTH(A1)>3),2)

Sat 14 Mar 2015

Tips & Tricks 82 – Date for Nth Day of the Year

By |Saturday, March 14th, 2015|Categories: Tips and Tricks|Tags: , , , |0 Comments

Suppose A1 contains the Year and you are asked to find 69th day of the year which is contained in A2. Then formula for finding Nth day of the year would be

=DATE(A1,1,1)+A2-1

Sat 21 Feb 2015

Tips & Tricks 79 – How to Know if a Year is a Leap Year

By |Saturday, February 21st, 2015|Categories: Tips and Tricks|Tags: , , |0 Comments

Let's say that A1 contains the year. To know whether it is a Leap Year or not, use following formula -

=MONTH(DATE(A1,2,29))=2

TRUE means that it is Leap Year and FALSE means that this is not a Leap Year.

Sat 13 Sep 2014

Article 5 - Difference Between Two Dates (Duration) in Terms of Months / Years

By |Saturday, September 13th, 2014|Categories: Articles|Tags: , , , , |0 Comments

DIFFERENCE IN MONTHS

There will be business scenarios when you will be asked to come up with duration between two dates in terms of months.

-- Note the Excel file for this article can be downloaded from DateDiff --

Let's Say A1 has From Date of 10/5/2013 and B1 has To Date.  You may use following formulas -

=DATEDIF(A1,B1,"m")

Now, this gives us the answer in Completed Months. It will not give fractional answer like 1.6 months.

(more…)