Calculate difference between hours

  • Thread starter Thread starter bren
  • Start date Start date
B

bren

Hi,

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:

Date and Hour in cell A1

01-11-2009 10:21 A.M

Date and Hour in cell B1

02-11-2009 4:43 P.M

The result should be 17:22 hours because we only calculate hours
between 8 AM and 7 PM

Thank you so much
 
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, LLC
www.cpearson.com
[email on web site]
 
Thank you so much, it works wonderfully, except in two cases:

1. If task starts before daystart. If I start at 7:32, it shouldn´t
add those 28 minutes from 7.32 to 8.00
2. If task is done on the same day

How can I improve this wonderful formula. Thank you so much.

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)*(Da­yEnd-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]

Hi,
Click to expand...
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:
Click to expand...
Date and Hour in cell A1
Click to expand...
01-11-2009 10:21 A.M
Click to expand...
Date and Hour in cell B1
Click to expand...
02-11-2009 4:43 P.M
Click to expand...
The result should be 17:22 hours because we only calculate hours
between 8 AM and 7 PM
Click to expand...
Thank you so much- Ocultar texto de la cita -
Click to expand...

- Mostrar texto de la cita -
Click to expand...
 
=(INT(B1)-INT(A1))*"11:00:00"+MIN(MOD(B1,1),"19:00:00")-MAX(MOD(A1,1),"08:00:00")

---
HTH
Bob Phillips

Thank you so much, it works wonderfully, except in two cases:

1. If task starts before daystart. If I start at 7:32, it shouldn´t
add those 28 minutes from 7.32 to 8.00
2. If task is done on the same day

How can I improve this wonderful formula. Thank you so much.

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)*(Da­yEnd-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]

Hi,
Click to expand...
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:
Click to expand...
Date and Hour in cell A1
Click to expand...
01-11-2009 10:21 A.M
Click to expand...
Date and Hour in cell B1
Click to expand...
02-11-2009 4:43 P.M
Click to expand...
The result should be 17:22 hours because we only calculate hours
between 8 AM and 7 PM
Click to expand...
Thank you so much- Ocultar texto de la cita -
Click to expand...

- Mostrar texto de la cita -
Click to expand...
 
Back
Top