Calculating diff between date/ time only between certain hours

[GMv1]

I am trying to calculate the difference in hours between a date/time range
but only the hours in a certain range.

1/27/10 4:49 1/27/10 11:32

I am trying to find the diff in hours only between 6:00 and 23:59, any hours
that fall outside of that time frame should not be counted. Each calculation
will have different dates so if there is a way to do this without specifying
the date that would be great!

 

[Daryl S]

GMv1 -

If your start date is in A4 and your end date is in B4, then you can do this:

=MOD(B4,1)-IF(MOD(A4,1)<0.25,0.25,MOD(A4,1))

This works becase date/times are stored as numbers. The date portion is the
integer piece, and the time is stored as the decimal piece. MOD pulls pulls
the remainder after removing all multiples of 1 (which in this case returns
the fraction piece of the date, which is the time). So MOD(B4,1) is just the
end time.

The start time portion just says if the time is less than 6AM (.25 = 1/4
day), then use .25 instead of the time (MOD(A4,1).

 

[Fred Smith]

Use:
=(INT(B1)-INT(A1))*18+(MOD(B1,1)-MAX(MOD(A1,1),TIME(6,0,0)))*24

Regards,
Fred

 

[GMv1]

Thank you this solution worked. I have another scenario that I have been
unable to solve editing the formula you supplied.
1/27/10 4:49 1/27/10 11:32

I am trying to find the diff in hours only between 8:00 and 17:00 Monday to
Friday only, any hours that fall outside of that time frame should not be
counted. Each calculation will have different dates so if there is a way to
do this without specifying the date that would be great!

Thanks again for all of your help thus far!

 

[Bernd P]

Hello,

I wrote a UDF for that:
http://sulprobil.com/html/count_hours.html

Regards,
Bernd

 

[GMv1]

This worked perfectly. Thank you!