Counting Hours in Multiple Days

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

avilliers@gmail.com - 27 Jul 2007 15:58 GMT

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

Reply to this Message

Pete_UK - 27 Jul 2007 16:15 GMT

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

On Jul 27, 3:58 pm, avilli...@gmail.com wrote:

> I'm attempting to do something that is currently beyond my skill
> level. Here's the problem:
[quoted text clipped - 17 lines]
>
> Alan

Reply to this Message

Rick Rothstein (MVP - VB) - 27 Jul 2007 17:21 GMT

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

> I'm attempting to do something that is currently beyond my skill
> level. Here's the problem:
[quoted text clipped - 17 lines]
>
> Alan

Reply to this Message

Rick Rothstein (MVP - VB) - 27 Jul 2007 22:48 GMT

> 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
[quoted text clipped - 6 lines]
>
> 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

Reply to this Message

Roger Govier - 27 Jul 2007 17:25 GMT

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

Signature

Regards
Roger Govier

> I'm attempting to do something that is currently beyond my skill
> level. Here's the problem:
[quoted text clipped - 17 lines]
>
> Alan

Reply to this Message

Bernd P - 28 Jul 2007 14:28 GMT

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

Reply to this Message

avilliers@gmail.com - 31 Jul 2007 22:45 GMT

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

Reply to this Message

Pete_UK - 31 Jul 2007 22:50 GMT

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

Thanks for feeding back.

Pete

On Jul 31, 10:45 pm, avilli...@gmail.com wrote:

> 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

Reply to this Message

Rate this thread: