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

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.

Sat 16 Apr 2016

Tips & Tricks 134 - Last Working Day of the Year

By |Saturday, April 16th, 2016|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

If a year is given in A1 say 2016, below formula can be used to know the last working day of the year (format the result as date)

=WORKDAY("1JAN"&A1+1,-1)

The above formula assumes that your weekends are Saturday and Sunday.

But, if your weekends are different (e.g. in gulf countries), you can use following formula -

=WORKDAY.INTL("1JAN"&A1+1,-1,"0000110")

Where 0000110 is a 7 character string, 1 represents a weekend and 0 is a working day. First digit is Monday and last digit is Sunday. The above example is for Gulf countries where Friday and Saturday are weekends.

You also have option to give a range which has holidays. In that case, your formula would become

=WORKDAY("1JAN"&A1+1,-1,D1:D10)

=WORKDAY.INTL("1JAN"&A1+1,-1,"0000110",D1:D10)

Where range D1:D10 contains the list of holidays.

Sat 02 Apr 2016

Tips & Tricks 133 - First Working Day of the Year

By |Saturday, April 02nd, 2016|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

If a year is given in A1 say 2016, below formula can be used to know the first working day of the year (format the result as date)

=WORKDAY(EOMONTH("1JAN"&A1,-1),1)

The above formula assumes that your weekends are Saturday and Sunday.

But, if your weekends are different (e.g. in gulf countries), you can use following formula -

=WORKDAY.INTL(EOMONTH("1JAN"&A1,-1),1,"0000110")

Where 0000110 is a 7 character string, 1 represents a weekend and 0 is a working day. First digit is Monday and last digit is Sunday. The above example is for Gulf countries where Friday and Saturday are weekends.

You also have option to give a range which has holidays. In that case, your formula would become

=WORKDAY(EOMONTH("1JAN"&A1,-1),1,D1:D10)

=WORKDAY.INTL(EOMONTH("1JAN"&A1,-1),1,"0000110",D1:D10)

Where range D1:D10 contains the list of holidays.

Sat 16 Jan 2016

Article 34 - Show Date for a Coming or Previous Day (Say Coming Monday, Previous Friday)

By |Saturday, January 16th, 2016|Categories: Articles|Tags: , , , , , , , , , , , , , , , , , |2 Comments

I had already written about one formula where I dealt with coming day formula - Article 3 – Show Date for a Coming Day (e.g. Coming Saturday)

I wanted to revisit this with following things in mind -

1. I wanted to explore various formulas on the above topic. Here, I am giving 2 additional formulas. So, I will be talking about a total of 3 formulas.

2. In Article 3, I talked only about Coming Day not the Previous Day. In this article, I will be talking about formulas for Previous Day also.

(more…)

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 07 Mar 2015

Tips & Tricks 81 – First Working Day of the Month if a Date is Given

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

If A1 contains a date, then formula for First Working Day of the month would be

=WORKDAY(EOMONTH(A1,-1),1)

The above formula assumes that your weekends are Saturday and Sunday.

But, if your weekends are different (e.g. in gulf countries), you can use following formula -

=WORKDAY.INTL(EOMONTH(A1,-1),1,"0000110")

Where 0000110 is a 7 character string, 1 represents a weekend and 0 is a working day. First digit is Monday and last digit is Sunday. The above example is for Gulf countries where Friday and Saturday are weekends.

You also have option to give a range which has holidays. In that case, your formula would become

=WORKDAY(EOMONTH(A1,-1),1,D1:D10)

=WORKDAY.INTL(EOMONTH(A1,-1),1,"0000110",D1:D10)

Where range D1:D10 contains the list of holidays.

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