Sat 01 Jul 2017

Tips & Tricks 161 - When is Thanksgiving Day in a Year

By |Saturday, July 01st, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |1 Comment

Last time, we discussed about finding Labor Day in a given year. This time, it is is about Thanksgiving Day in a year. Thanksgiving day is 4th Thursday in a November.

Hence, earliest possible day when 4th Thursday can happen is on 22-Nov.


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 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: , , , , , , , |0 Comments

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 -