date and time in 2 cells to create subtraction in 3rd

[Guest]

I have 2 cells that are in the format line of

A1: 27/06/2006 9:42:00 pm
B1: 7/06/2006 9:52:05 pm

Want third column C1: 00:10


I wish to subtract the two cells to get the difference in time i.e 10:00
(only interested in nearest minute)

Then a average at the end of the column of the the times in this 3rd column
as a result of the difference between the 2 times.

How would I do this?

I could do it if it was just numbers but once it is in this time/dtae format
I am completely lost!

Thanks
MIke

 

[Niek Otten]

Hi Mike,

=MOD(B1,1)-MOD(A1,1)

Format as Time

You can use the AVERAGE function on the column, again, format as Time

Time in Excel is stored as a fraction of a day

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have 2 cells that are in the format line of
|
| A1: 27/06/2006 9:42:00 pm
| B1: 7/06/2006 9:52:05 pm
|
| Want third column C1: 00:10
|
|
| I wish to subtract the two cells to get the difference in time i.e 10:00
| (only interested in nearest minute)
|
| Then a average at the end of the column of the the times in this 3rd column
| as a result of the difference between the 2 times.
|
| How would I do this?
|
| I could do it if it was just numbers but once it is in this time/dtae format
| I am completely lost!
|
| Thanks
| MIke
|

 

[Bob Phillips]

=B1-A1

etc

=AVERAGE(C1:C100)

as simple as that

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)