OK....I *think* I understand what you're asking for...
and I think I figured out what you *need*.
Let's see if I'm right.
(I attached a demo file to this post)
Here's the set up:
A1: Test Date
B1: (a date to test coverage for....eg 12/20/2007)
A2: START
B2: END
C2:Z2....contains raw times formatted as "hh:mm":
C2: 12:00:00 AM......displays as 00:00
D2: 01:00:00 AM......displays as 01:00
etc....
Z2: 11:00:00 PM......displays as 23:00
A3: (a start date/time....eg 12/20/2007 1:00:00 AM)
B3: (an end date/time....eg 12/20/2007 5:15:00 AM)
Now for the calculations....
This formua calculates how much of the
hour beginning with the value in Row_2
and IN the date in B1 is covered by the range
in A3:B3, displayed as a decimal 0 through 1)
Copy that formula across through Z3
NOTE:
The calculations only pertain the portion of the
start through end date/time that are within the DATE in B1.
Bookmarks