On Sat, 27 Jun 2009 13:01:01 -0700, Ron
wrote:


I need to sum the length of tim in column d: according to the hour in column
B:.

Column B = 8:44:00AM or 8:44AM (from a text file, general format.)
Column D - 16:38, stored as 4:38:00 PM (formatted h:mm, but actually min/sec)

I have already changed B to military with:
VALUE(IF(ISNA(CONCATENATE(LEFT(B3,LEN(B3)-2),"
",RIGHT(B3,2))),"",CONCATENATE(LEFT(B3,LEN(B3)-2)," ",RIGHT(B3,2)))) (Not my
formula, but the damn thing works)

B: is sorted so the hours are in order. so I'm sure there is a better way
than pasting about 2000 formulas in a spreadsheet

Thanks,

Ron

http://office.microsoft.com/search/r... T101172771033

Standard sum functions work with time as the time is ALWAYS stored
internally as a single number by Excel, regardless of how you make it
appear on the sheet, so you likely do not need to go through all of that
if you simply use input validation functions to force the user to input
the correct data to begin with.

Take a look at the sheet. Use whatever you want from it. Enjoy.