Time Cell help

[SFD]

I have a spreadsheet that has the been exported from a data base into excel.

the cells have times in them as -

03:24:00 03:28:00 03:31:00 00:00:00 03:41:00 03:52:00 04:04:00
04:19:00


When I go to cell property it shows it as "general"

I have close to 3000 lines as above.

I need a way to calculate 03:24:00 and 04:19:00 to total 00:55 in another
cell

1) How can I convert the cells to hr mm and not delete the data

2) How do I calculate the SUM of minutes to get a total amount of minutes
for all the times?

Any help is greatly appreciated!!

Steve

 

[Pete_UK]

If I understand correctly, you have eight dates in one cell, each
separated by a single space. If you want to get these into 8 different
columns you can highlight the column then do Data | Text-to-Columns and
specify space as the delimiter. If you then want the difference between
column H and column A, enter this formula in cell i1:

=H1-A1

then copy this formula down your 3000 rows.

Hope this helps.

Pete

 

[SFD]

Sorry Pete I should have clarified it a little better:

What I have are eight cells in one row each with

A1 A2 A3 A4 A5
A6 A7 A8
03:24:00 03:28:00 03:31:00 00:00:00 03:41:00 03:52:00 04:04:00
04:19:00

These are "times" 03:24:00 is 03:24 am/0324hrs

Sorry for the confusion

Steve

 

[Pete_UK]

Another confusion now is your cell referencing - it normally goes A, B,
C etc going across ...

Given that the dates are already in 8 separate cells, will my second
suggestion not work now?

i.e. to enter this formula in column i: =H1-A1

and copy down.

Pete

 

[SFD]

Pete,

You are correct, very long night, yes it should have been A1,B1,C1 ect ....

Yes your formula worked for about 90% of the rows .... The other 10% are
when the time cross's from 23:30:00 - 00:55:00 gives me no total.

Thanks for the help and sorry about the confusion.

Steve

 

[neillcato]

Steve,

Try

=IF(A1>H1,H1+24-A1,H1-A1)

and fill down.

Neill

 

[Pete_UK]

As these are times, where 1 = 24hour day, you need to add 1 not 24, so
amend this formula:

=IF(A1>H1,H1-A1+1,H1-A1)

Hope this helps.

Pete