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 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: , , , , |1 Comment

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 28 Feb 2015

Tips & Tricks 80 – Last Working Day of the Month If a Date is Given

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

If A1 holds a date, the formula for calculating last Working Day of the month would be

=WORKDAY(EOMONTH(A1,0)+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,0)+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,0)+1,-1,D1:D10)

=WORKDAY.INTL(EOMONTH(A1,0)+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

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