Assuming that you don't need to exclude any full days (e.g., weekends)

between the start and end dates, you can use a formula like the

following:

=MAX(0,DayEnd-MOD(TStart,1))+MAX(0,TRUNC(TEnd,0)-TRUNC(TStart,0)-1)*(DayEnd-DayStart)+MAX(0,MOD(TEnd-DayStart,1))

Here, DayEnd is the ending time of a work day, e.g, 17:00, with no

date component. DayStart is the start time of a work day, e.g., 9:00,

with no date component. TStart is the full date and time that the task

started, and TEnd is the full date and time that the task ended. This

returns the number of hours between TStart and TEnd that fall between

DayStart and DayEnd. The result is a time,. e.g., 53:30:00. To get the

number of hours, e.g., 53.50, multiply the entire formula by 24:

=24*(MAX(0,DayEnd-MOD(TStart,1))+MAX(0,TRUNC(TEnd,0)-TRUNC(TStart,0)-1)*(DayEnd-DayStart)+MAX(0,MOD(TEnd-DayStart,1)))

It is assumed that the task in question begins at or later than

DayStart and ends at or earlier than DayEnd.

For example, if your work day starts at 9:00 AM (DayStart) and ends at

5:00 PM (DayEnd) and the task at hand starts (TStart) on 1-Jan-2009

11:00 AM and ends (TEnd) on 7-Jan-2009 at 4:00 PM, the formula returns

53:00:00 which is 6 hours the first day (5:00 PM - 11:00 AM) + 5

complete days (8:00 hours each) + 7:00 (16:00 - 9:00) hours on the

last day.

Cordially,

Chip Pearson

Microsoft MVP 1998 - 2010

Pearson Software Consulting, LLCwww.cpearson.com

[email on web site]

I have a sheet with some dates and hours. I need some formula or

function that calculates how many time has gone between both hours but

knowing that it only must count the hours between 8 A.M and 7 P.M.

Example:

The result should be 17:22 hours because we only calculate hours

between 8 AM and 7 PM

Thank you so much- Ocultar texto de la cita -

- Mostrar texto de la cita -