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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MikeR-Oz - 27 Oct 2007 10:25 GMT

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

Reply to this Message

Niek Otten - 27 Oct 2007 10:33 GMT

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

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have 2 cells that are in the format line of
|
[quoted text clipped - 16 lines]
| Thanks
| MIke

Reply to this Message

Bob Phillips - 27 Oct 2007 10:35 GMT

=B1-A1

etc

=AVERAGE(C1:C100)

as simple as that

Signature

HTH

Bob

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

>I have 2 cells that are in the format line of
>
[quoted text clipped - 18 lines]
> Thanks
> MIke

Reply to this Message