How to subtract[difference] in TIMES??

[Crackles McFarly]

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"?

 

[JE McGimpsey]

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)

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[David Biddulph]

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

 

[Crackles McFarly]

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)

thanks folks,,,

 

[Crackles McFarly]

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.

 

[Peo Sjoblom]

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

 

[David Biddulph]

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.

 

[Crackles McFarly]

WOW, too easy hu?

thanks a bunch