How to subtract[difference] in TIMES??
 

12:15 pm
12:44 pm
12:45 pm
12:59 pm
then
12:01 am
12:09 am
12:19 am

The differences aren't the same, they change and that is that Number I
wish to calculate/display in another Cell

With about 22 entries in between, not of the same interval.

I'd REALLY appreciate some help with this problem.

How can you subtract the times each time so you can Display the "time
interval"?

How to subtract[difference] in TIMES??
 

XL stores times as fractional days, so one can simply subtract the
earlier one from the later one. However, if the times span midnight, the
"later" time will actually be smaller than the earlier time (e.g., 3:00
am = 0.125, 6:00 pm = 0.75), so one needs to correct for that span. One
way:

=A22-A1 + (A22<A1)

takes advantage of XL's coercing TRUE/FALSE to 1/0.

Another:

=MOD(A22-A1,1)





In article ,
Crackles McFarly wrote:

12:15 pm
12:44 pm
12:45 pm
12:59 pm
then
12:01 am
12:09 am
12:19 am

The differences aren't the same, they change and that is that Number I
wish to calculate/display in another Cell

With about 22 entries in between, not of the same interval.

I'd REALLY appreciate some help with this problem.

How can you subtract the times each time so you can Display the "time
interval"?

How to subtract[difference] in TIMES??
 

Witht the times in Column B starting from B3 use:

=MOD(B4-B3,1)

and copy down using the fill handle.

It will successfully cross midnight.



--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Crackles McFarly" wrote in message
...
12:15 pm
12:44 pm
12:45 pm
12:59 pm
then
12:01 am
12:09 am
12:19 am

The differences aren't the same, they change and that is that Number I
wish to calculate/display in another Cell

With about 22 entries in between, not of the same interval.

I'd REALLY appreciate some help with this problem.

How can you subtract the times each time so you can Display the "time
interval"?

How to subtract[difference] in TIMES??
 

Some good explanations and examples:-

http://cpearson.com/excel/datearith.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Crackles McFarly" wrote in message
...
12:15 pm
12:44 pm
12:45 pm
12:59 pm
then
12:01 am
12:09 am
12:19 am

The differences aren't the same, they change and that is that Number I
wish to calculate/display in another Cell

With about 22 entries in between, not of the same interval.

I'd REALLY appreciate some help with this problem.

How can you subtract the times each time so you can Display the "time
interval"?

How to subtract[difference] in TIMES??
 

If you're just trying to find time time diffrerence between consecutive
rows, then use =MOD(A3-A2,1) [as the MOD function will cope with wrapping
round at midnight]
Format the result as [mm] if you want it shown in minutes, or as [hh]:mm if
you want hours and minutes.
If you want the result available in minutes for further calculations, then
use =MOD(A3-A2,1)*24*60
--
David Biddulph

"Crackles McFarly" wrote in message
...
12:15 pm
12:44 pm
12:45 pm
12:59 pm
then
12:01 am
12:09 am
12:19 am

The differences aren't the same, they change and that is that Number I
wish to calculate/display in another Cell

With about 22 entries in between, not of the same interval.

I'd REALLY appreciate some help with this problem.

How can you subtract the times each time so you can Display the "time
interval"?

How to subtract[difference] in TIMES??
 

On Tue, 21 Aug 2007 11:44:55 -0600, JE McGimpsey
sayd the following:

XL stores times as fractional days, so one can simply subtract the
earlier one from the later one. However, if the times span midnight, the
"later" time will actually be smaller than the earlier time (e.g., 3:00
am = 0.125, 6:00 pm = 0.75), so one needs to correct for that span. One
way:

=A22-A1 + (A22<A1)

takes advantage of XL's coercing TRUE/FALSE to 1/0.

Another:

=MOD(A22-A1,1)





In article ,
Crackles McFarly wrote:

12:15 pm
12:44 pm
12:45 pm
12:59 pm
then
12:01 am
12:09 am
12:19 am

The differences aren't the same, they change and that is that Number I
wish to calculate/display in another Cell

With about 22 entries in between, not of the same interval.

I'd REALLY appreciate some help with this problem.

How can you subtract the times each time so you can Display the "time
interval"?


thanks folks,,,

How to subtract[difference] in TIMES??
 

On Tue, 21 Aug 2007 16:20:24 -0400, Crackles McFarly
sayd the following:

On Tue, 21 Aug 2007 11:44:55 -0600, JE McGimpsey
sayd the following:

XL stores times as fractional days, so one can simply subtract the
earlier one from the later one. However, if the times span midnight, the
"later" time will actually be smaller than the earlier time (e.g., 3:00
am = 0.125, 6:00 pm = 0.75), so one needs to correct for that span. One
way:

=A22-A1 + (A22<A1)

takes advantage of XL's coercing TRUE/FALSE to 1/0.

Another:

=MOD(A22-A1,1)





In article ,
Crackles McFarly wrote:

12:15 pm
12:44 pm
12:45 pm
12:59 pm
then
12:01 am
12:09 am
12:19 am

The differences aren't the same, they change and that is that Number I
wish to calculate/display in another Cell

With about 22 entries in between, not of the same interval.

I'd REALLY appreciate some help with this problem.

How can you subtract the times each time so you can Display the "time
interval"?


thanks folks,,,


Hold up a sec folks.

I noticed the only way to get a result you can read or know is to set
the cell with the answer as a TIME format..I noticed their is no way
to just get some result number like 22 or 55, it has to be some format
like 15:00:00 or 00:15:00 for 15 mins as an example.

Anyway to get a result which is just a single number?


thanks a lot.

How to subtract[difference] in TIMES??
 

If you want integer minutes multiply by 1440 (value*24*60) and format as
general, if you just want to change the format use a custom format of [mm]
to format 00:15:00 to 15
To get decimal hours multiply with 24 and format as general or number


--
Regards,

Peo Sjoblom



"Crackles McFarly" wrote in message
...
On Tue, 21 Aug 2007 16:20:24 -0400, Crackles McFarly
sayd the following:

On Tue, 21 Aug 2007 11:44:55 -0600, JE McGimpsey
sayd the following:

XL stores times as fractional days, so one can simply subtract the
earlier one from the later one. However, if the times span midnight, the
"later" time will actually be smaller than the earlier time (e.g., 3:00
am = 0.125, 6:00 pm = 0.75), so one needs to correct for that span. One
way:

=A22-A1 + (A22<A1)

takes advantage of XL's coercing TRUE/FALSE to 1/0.

Another:

=MOD(A22-A1,1)





In article ,
Crackles McFarly wrote:

12:15 pm
12:44 pm
12:45 pm
12:59 pm
then
12:01 am
12:09 am
12:19 am

The differences aren't the same, they change and that is that Number I
wish to calculate/display in another Cell

With about 22 entries in between, not of the same interval.

I'd REALLY appreciate some help with this problem.

How can you subtract the times each time so you can Display the "time
interval"?


thanks folks,,,


Hold up a sec folks.

I noticed the only way to get a result you can read or know is to set
the cell with the answer as a TIME format..I noticed their is no way
to just get some result number like 22 or 55, it has to be some format
like 15:00:00 or 00:15:00 for 15 mins as an example.

Anyway to get a result which is just a single number?


thanks a lot.

How to subtract[difference] in TIMES??
 

"Crackles McFarly" wrote in message
...
On Tue, 21 Aug 2007 16:20:24 -0400, Crackles McFarly
sayd the following:

On Tue, 21 Aug 2007 11:44:55 -0600, JE McGimpsey
sayd the following:

XL stores times as fractional days, so one can simply subtract the
earlier one from the later one. However, if the times span midnight, the
"later" time will actually be smaller than the earlier time (e.g., 3:00
am = 0.125, 6:00 pm = 0.75), so one needs to correct for that span. One
way:

=A22-A1 + (A22<A1)

takes advantage of XL's coercing TRUE/FALSE to 1/0.

Another:

=MOD(A22-A1,1)

In article ,
Crackles McFarly wrote:

12:15 pm
12:44 pm
12:45 pm
12:59 pm
then
12:01 am
12:09 am
12:19 am

The differences aren't the same, they change and that is that Number I
wish to calculate/display in another Cell

With about 22 entries in between, not of the same interval.

I'd REALLY appreciate some help with this problem.

How can you subtract the times each time so you can Display the "time
interval"?

thanks folks,,,

Hold up a sec folks.

I noticed the only way to get a result you can read or know is to set
the cell with the answer as a TIME format..I noticed their is no way
to just get some result number like 22 or 55, it has to be some format
like 15:00:00 or 00:15:00 for 15 mins as an example.

Anyway to get a result which is just a single number?

Yes. See the replies you had earlier.
--
David Biddulph
Rowing web pages at
http://www.biddulph.org.uk/

How to subtract[difference] in TIMES??
 

WOW, too easy hu?

thanks a bunch

On Tue, 21 Aug 2007 13:36:19 -0700, "Peo Sjoblom"
sayd the following:

If you want integer minutes multiply by 1440 (value*24*60) and format as
general, if you just want to change the format use a custom format of [mm]
to format 00:15:00 to 15
To get decimal hours multiply with 24 and format as general or number