Counting Hours in Multiple Days

[avilliers]

I'm attempting to do something that is currently beyond my skill
level. Here's the problem:

I have the following columns: Start Date/Time, End Date/Time. I want
to count the number of hours each day for each day of the period. I
can get the total number of hours easily. However given:

Start Date/Time End Date/Time
07/29/2007 15:00 07/31/2007 20:00

That's a total of 57 hours. What I am looking for is a way to break
out each day:

Day 1 = 13h
Day 2 = 24h
Day 3 = 20h
Day 4, 5, 6 - Day 31

Ideas greatly appreciated.

Alan

 

[Pete_UK]

So, you could potentially have 31 days difference between the start
and end dates? Does this mean that you want the results to span 31
cells?

Pete

 

[Rick Rothstein \(MVP - VB\)]

Since you didn't say, let's assume the Start Date/Time is in A1 and the End
Date/Time is in B1, then put this

=24*IF($B$1-$A$1>(1-MOD(A1,1)),1-MOD(A1,1),B1-A1)

in C1 and this

=IF($B$1-$A$1>(1-MOD(A1,1)),IF($B$1-$A$1>=ROWS($1:1),24,IF(INT($B$1-$A$1+1)=ROWS($1:1),24*($B$1-INT($B$1)),"")),"")

in C2. Copy C2 down as far as needed.

Rick

 

[Roger Govier]

Hi

With first date and time in A1, second date and time in A2 and A4:A34
containing the numbers 1 through 31
In B1
=24-HOUR(A1)
in B2
=(A2-A1)*24 (which equals 53 hours, not 57)

In B4
=IF(DAY($A$1)=A4,$B$1,IF(AND(DAY($A$2)>=A4,DAY($A$1)<=A4),MIN(24,($B$2-SUM($B$3:B3))),0))
Copy down through B5:B34

 

[Rick Rothstein \(MVP - VB\)]

Since you didn't say, let's assume the Start Date/Time is in A1 and the

End Date/Time is in B1, then put this

=24*IF($B$1-$A$1>(1-MOD(A1,1)),1-MOD(A1,1),B1-A1)

in C1 and this

=IF($B$1-$A$1>(1-MOD(A1,1)),IF($B$1-$A$1>=ROWS($1:1),24,IF(INT($B$1-$A$1+1)=ROWS($1:1),24*($B$1-INT($B$1)),"")),"")

in C2. Copy C2 down as far as needed.

CORRECTION: I left out some absolute references in the original first
formula and the second original formula added an extra 24 hours. Below are
the corrections for the two formulas. As now corrected, these formulas are
no longer restricted to being in C1 and C2 (and down).

Still assuming the Start Date/Time is in A1 and the End Date/Time is in B1;
then put this formula...

=24*IF($B$1-$A$1>(1-MOD($A$1,1)),1-MOD($A$1,1),$B$1-$A$1)

in any cell you want (except A1 or B1 of course<g>); and put this formula...

=IF($B$1-$A$1>(1-MOD(A1,1)),IF($B$1-$A$1-1>=ROWS($1:1),24,IF(INT($B$1-$A$1)=ROWS($1:1),24*($B$1-INT($B$1)),"")),"")

in the cell underneath the one you placed the first formula in and then copy
it down as far as needed.

Rick

 

[Bernd P]

Hello Alan,

It's kind of overkill but you can use my UDF count_hours. Put start
date into A1 and end date into A2:
=count_hours(A1,A2,{0,1;0,1;0,1;0,1;0,1;0,1;0,1})

My UDF you can find here:
http://www.sulprobil.com/html/count_hours.html

Press ALT + F11, insert a new module, copy and paste my macro code
into this module and go back to your worksheet.

Regards,
Bernd

 

[avilliers]

Thank you all very much, Bernd, Rick, Roger and Pete! All of the
solutions were elegant and very much appreciated. Now the task of
learning how this all works! Again, much appreciated - took care of my
need.

Alan

 

[Pete_UK]

Ah well, it's nice to get praise for not offering a solution !! <bg>

Thanks for feeding back.

Pete