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

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.

Mon 13 Jul 2015

Solution - Challenge 9 – Get the Customer Spending Maximum Amount for a given Month and Starting Alphabet

By |Monday, July 13th, 2015|Categories: Solutions|Tags: , , , , , |1 Comment

Below is a possible solution for the challenge - Challenge 9 – Get the Customer Spending Maximum Amount for a given Month and Starting Alphabet

Put following Array formula in H4

=INDEX(D2:D20,MATCH(1,--(MAX(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20)))
=(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20)))),0))

Put following Array formula in H5

=INDEX(E2:E20,MATCH(1,--(MAX(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20)))
=(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20)))),0))

Note - Array Formula is not entered 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 { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

The solution sheet can be downloaded from here - Solution-Challenge-9-–-Get-the-Customer-Spending-Maximum-Amount-for-a-given-Month-and-Starting-Alphabet

Sat 14 Feb 2015

Tips & Tricks 78 - Number of Days in a Month

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

Suppose, you have been given a date say 15-Nov-14 and you have to determine how many days this particular month contains.

The formula which you need to use in the above case would be

=DAY(EOMONTH(A1,0))

Explanation - EOMONTH(A1,0) gives the last date of the month and DAY function extract that particular Day from the last date of the month.

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

Sat 23 Aug 2014

Tips & Tricks 43 - Last Day of the Month for a Given Date

By |Saturday, August 23rd, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

Suppose, you are given a date say 10/22/14 (MM/DD/YY) and we want to have the last date of the month for the given date. Hence, you needs an answer of 10/31/14. The formulas to be used in this case -

=EOMONTH(A1,0)

=DATE(YEAR(A1),MONTH(A1)+1,0)

=DATE(YEAR(A1),MONTH(A1)+1,1)-1

Sat 12 Jul 2014

Tips & Tricks 31 - Generate Month Names Jan to Dec in Text or January to December

By |Saturday, July 12th, 2014|Categories: Tips and Tricks|Tags: , , , , , , , , , , , , , |0 Comments

1. Put "Jan" without quotes in a Cell. Let's assume this is in cell A1 here. If you want to generate full names of the month, put "January" without quotes.
Note - Start month can be any other month also. "Jan" has been chosen for illustration purpose only. The output will be starting with the month whatever has been put in cell A1.

2. Drag the bottom left corner till row 12 by pressing left click. It will automatically generate the names of the month.

OR

3. Drag the bottom left corner till row 12 by pressing right click.

4. Leave Right Click.

5. From the menu which pops up, select Fill Months.

1

Sat 05 Jul 2014

Tips & Tricks 28 - First Day of the Month for a Given Date

By |Saturday, July 05th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

Suppose you have been given a date say 10/22/14 (MM/DD/YY) and you want to calculate the first day of the Current Month. Hence, you want to achieve a result of 10/1/2014 (MM/DD/YY).

The formulas to be used -

=DATE(YEAR(A1),MONTH(A1),1)

=A1-DAY(A1)+1

=EOMONTH(A1,-1)+1