Sat 06 Aug 2016

Tips and Tricks 142 - Determine Number of Working Days in a Year

By |Saturday, August 06th, 2016|Categories: Tips and Tricks|Tags: , , , , , , , |1 Comment

Suppose, you have been given a year in A1 (Say A1 = 2016) and you need to determine the number of working days in a Year, then your formula to determine number of working days would be -


The above formula is based on the fact that Saturdays and Sundays are weekends. Starting Excel 2010, you can control the weekends in the formula and function is NETWORKDAYS.INTL


In the string "000110" - First digit is Monday and last digit is Sunday. 1 defines that particular day as weekend.

If you have got your list of holidays in a range say B1:B20 (B1:B20 should contain dates in date format), you can have following formulas



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.


Sat 19 Dec 2015

Article 32 - Calculate Working Hours between Two Ranges and Exclude Weekends and Holidays (SLA Calculation)

By |Saturday, December 19th, 2015|Categories: Articles|Tags: , , , , , , , |1 Comment

Suppose you have two time stamps say A1: 23-Dec-2015 09:15 AM and B1: 29-Dec-2015 02:30 PM and say your working hours are between 09:00 AM to 05:00 PM. You have been tasked to calculate the working hours between these two dates and you need to exclude weekends (here - 26-Dec-15 and 27-Dec-15) and any holiday (here - 25-Dec-15). Below is the detail calculation for this -