Sun 14 Jul 2019

Tips & Tricks 171 - Calculate Next Working day if date is of Weekend / Holiday

By |Sunday, July 14th, 2019|Categories: Tips and Tricks|Tags: , , |0 Comments

Suppose you are given a date and you are asked to calculate next working day if date is of weekend. If date is a regular workday, then you should show the same date.

For example - 8-Mar-19 is a working day. Hence, you should show the same date. But if this is either 9-Mar-19 or 10-Mar-19 which are Saturday and Sunday, then you must show 11-Mar-19 as the next workday. (more…)

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