#ExcelPedroWave

What do I spend the hours between dates of job change?

In writing post #100 of this blog, that will soon reach the 200,000 page views, but really I have written 50 original articles in two versions, English and Spanish.

What is the purpose of this article?

I share with you a public Excel template, that it can be downloaded below, used to obtain the number of hours between two dates, with up to 4 different time ranges and without the condition that the start and end times are within the work hours, allowing you to choose which days of the week are working days (traditional weekend of Saturday and Sunday or just Sunday or any other day).

Next scheme is used to calculate one hour range, extrapolating it is used to calculate different hour ranges, simply adding them.

Who would this template may be useful?

To Human Resources professionals, project managers, budgets managers that calculate manhours efforts or hours-person, part-time workers, the moonlighters, teachers and professors to organize their classes and, in general, those who need to know how many hours dedicated to training, work, leisure, etc..

What inspired me?

Over two years ago I was doing Excel calendars for this blog and my friend Chandoo threw out a challenge: "How many hours did Johnny work? [Excel Homework]"

How I solved the challenge?

I immediately started calculating the hours worked between two dates by Johnny, excluding Saturdays, Sundays and holidays and I got this formula which was very successful in its simplicity:
=9*(NETWORKDAYS(B3,C3)-1)+24*(MOD(C3,1)-MOD(B3,1))
Being B3 the start date and time and C3 the end date and time, working for nine hours.

Chandoo congratulated me on this formula in his next article "How to Calculate Working Hours Between 2 Dates [Solution]" and a few supporters began to discuss our formulas, including Excel gurus as Daniel Ferry and Chip Pearson.

How to calculate a more general solution?

The above formula and the proposals by Daniel Ferry, Michael, sixseven and Elias had a condition that both start and end hours are within working hours and Chandoo challenge us again to solve a problem that could be even more interesting, get working hours between two dates without the condition that both the start time and end time are within working hours.

I started to collect and analyze all the proposals of my fellow forum Chandoo.org and everything I found on the Web and, not seeing a smart solution, I started to develop for 5 days to get this new formula:

=(DecimalHours*(NETWORKDAYS(B3,C3,HolidayList)-1)
+ IF(NETWORKDAYS(C3,C3,HolidayList)=0,$D$29,MAX($D$28,MIN(MOD(C3,1),$D$29)))
- IF(NETWORKDAYS(B3,B3,HolidayList)=0,$D$28,MIN($D$29,MAX(MOD(B3,1),$D$28))))*24

Where:
DecimalHours = $D$29 - $D$28 (number of daily working hours)
$D$28 = Start work hour
$D$29 = End work hour

What makes me take up again the calculation of hours between dates?

Four days ago a query posed by jcascon11 in the AyudaExcel forum:

Calculate time between two dates in hours without holidays or weekends and with a time range

I need to calculate the time between start date and end date excluding holidays, Saturdays and Sundays and this the time range:
Monday to Thursday working hours are from 8:00 to 15:00 and 17:00 to 19:30
Fridays from 8:00 to 15:00
Anybody can help me?

How to calculate the hours over a time range of work?

You can download the file with the calculations based on the version of Excel:
- Column D for Excel 2003 and 2007, using the auxiliary columns E:K
- Column C for Excel 2010 and later, using the new function:
WORKDAY.INTL(start_date, days, [weekend], [holidays])

Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. 1111111 is an invalid string. For example, 0000011 would result in a weekend that is Saturday and Sunday.

In both cases I use variations of the formula I proposed more than two years ago in the Chandoo, but in Excel 2010 you can do it with a single megaformula and in earlier versions of Excel you need auxiliary columns to do the same.

Where do I download the template?

Clicking on these links:

Why not try it yourself in the cloud?

This is the interactive template for those without Excel 2010 and for those want to see the result of the total hours in column C with this version of Excel. You can modify all cells with yellow background.

As Microsoft does not provide data validations in Excel Web App, a mandatory 0 (working day) or 1 (non-working day) must be entered in the range E2 to K5.

How I tested if the calculation of the hours is correct?

Introducing ranges of different days, with the aid of these two formulas that are based on the RAND() function included in the cell A25:B34 for the start date (column A) and end date (column B):

=ROUND((RAND()*(41640-41275)+41275),2)
=ROUND(RAND()*(41640-A25)+A25,2)

The chosen values are 41275 to 01/01/2013 and 41640 to 01/01/2014.

I compare in the column L the hours calculated in Excel 2010 (column C) with other versions (column D) with the following formula:
=IF(ROUND(C25,5)=ROUND(D25,5),"OK","KO")

Where keep learning to calculate dates and times in Excel?

Although there are many sites like Chandoo's blog, and not bad to begin with the site by Chip Pearson: Dates And Times In Excel

Will I continue writing articles on this blog?

Until now I have to greatly appreciate the welcome that is having this blog among the curious who come to the graphical user interfaces and looking for help to solve their calculations in Excel.

Depending on the time I have, henceforth I will do my best to continue contributing with ideas and examples of these two wonderful worlds, trying to bring aid to my readers. What is certain is that I used to plan and solve problems and order to challenge myself and try to experiment and master a little more each day these waves of calculation and programming.