Hours between two dates

Posted on lunes, marzo 18, 2013 by Pedro Wave

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:

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:
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):
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:


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.

2 Response to "Hours between two dates"

.
gravatar
Unknown Says....

Hi There,


I had a very similar requirement for a sheet and thanks a lot for making this sheet public. This exactly fits my requirement apart from only one thing that is, if in range i put in the time as 2AM - 7 PM i get the desired result.

But if i have a timing like 7 PM - 2 AM it doesnt work properly. Would you be kind enough to share some thoughts on it please

.
gravatar
Nabeel Rehman Says....

Hello there,

Thanks for sharing this file and it fits my exact requirement. The only issue i have that i get the actual durations between time range as long as the start and end times are as follows:
AM-AM, AM-PM. It does not work properly if the duration i have is from PM-AM. Kindly help on this please.

Leave A Reply

Dime si te gusta lo que lees y, si no te gusta, dime por qué. Tengo habilitada la moderación de comentarios. Tu comentario se publicará pronto.

Tell me if you like what you read here and if you don't like, tell me why. I've enabled comment moderation. Your comment will be published ASAP.

Mi Lista de Blogs- My Blog List