Welcome to the Board
I'm sure daddylonglegs himself will look on this and provide a more elegant method but I guess I'd probably opt for something like the below...
NOTE: the below utilises functions DOLLARDE & DOLLARFR - both require activation of the Analysis ToolPak in Excel 2003 via Tools -> Add-Ins.
A1: (onwards) contain Start Times in format of hh:mm:ss:ff
B1: (onwards) contain End Times in format of hh:mm:ss:ff
where ff has a base of 24
I'd probably be inclined to convert Start & End values into Seconds based values in adjacent cells, such that
C1: =IF(A1="","",DOLLARDE(SUBSTITUTE(A1,":",".",3)*86400,24))
copied down and across to D as required
The difference can then be shown in terms of seconds.frames using
E1: =IF(COUNT(C1:D1)=2,DOLLARFR(D1-C1,24),0)
copied down as required
To then sum the durations (with the base of 24 f per sec), if we assume we have times in rows 1 to 10 we can calculate the total duration in terms of sec.frames using:
E11:
=DOLLARFR(SUMPRODUCT(INT($E$1:$E$10))+DOLLARDE(SUMPRODUCT(MOD($E$1:$E$10,1)),24),24)
In terms of displaying the above in hh:mm:ss ... what do you want to do with the frames ? You could show as milliseconds if that's your wish ?
To do so, add a division of 86400 to the above formula and format E11 as hh:mm:ss.00
To reiterate I'm convinced there's a better, more elegant method which dll will provide in due course - hopefully in the meantime the above will help you progress the project ?
Bookmarks