elapsed dates between start and end date and time

[flick]

I am looking for help with the following problem for an Excel 2003 spreadsheet.

I have 2 colums, 1 with start time and date of an event, the other with the
end time and date. I am looking for a way to determine the elapsed time in
hours per day, expressed as a date, within the specified period, spread over
2 columns

Input:
Column 1 Column 2
14-10-08 22:00 16-10-08 15:00

Output:
Column 3 Column 4
14-10-08 2 hrs
15-10-08 24 hrs
16-10-08 15 hrs

Very grateful for any help.

 

[ShaneDevenshire]

Hi,

Assume your data starts in A2 with titles on row 1, in C2 enter the formula

=DATE(YEAR($A$2),MONTH($A$2),DAY($A$2))+ROW(A1)-1

Copy this down as far as necessary to create the dates in the third column
or you can just manually enter them.

In D2 enter the following formula

=IF(ROW()=2,IF(C2<TRUNC($B$2),1,MOD($B$2,1))-MOD($A$2,1),IF(C2<TRUNC($B$2),1,MOD($B$2,1)))

and copy it down as far as necessary.


If this helps, please click the Yes button.

 

[Herbert Seidenberg]

Excel 2007
convertible to 2003
European date format
Covers multiple events:
http://www.savefile.com/files/1853124