Sat 01 Jul 2017

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

By |Saturday, July 01st, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

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.

(more…)

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 -

=NETWORKDAYS("1JAN"&A1,"31DEC"&A1)

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

=NETWORKDAYS.INTL("1JAN"&A1,"31DEC"&A1,"0000110")

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

=NETWORKDAYS("1JAN"&A1,"31DEC"&A1,B1:B20)

=NETWORKDAYS.INTL("1JAN"&A1,"31DEC"&A1,"0000110",B1:B20)

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 -

1

(more…)