How can I calculate the difference between times without using th.
 

I would like to use Excel to calculate total minutes between two times,
without using the colon sign (it would be so much faster). 1315 to 1525
would be 130" not 210.

Assuming one of your times is in A1

=INT(A1 / 100) * 60 + MOD(A1, 100)

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"KELSEY1022" wrote in message
...
I would like to use Excel to calculate total minutes between two times,
without using the colon sign (it would be so much faster). 1315 to 1525
would be 130" not 210.

Kelsey

To enter dates and times without using a separator you would need VBA code.

See Chip Pearson's site......

http://www.cpearson.com/excel/DateTimeEntry.htm

Also see his site for Time Arithmetic

http://www.cpearson.com/excel/datetime.htm#AddingTimes


Gord Dibben Excel MVP

On Sat, 22 Jan 2005 12:43:02 -0800, "KELSEY1022"
wrote:

I would like to use Excel to calculate total minutes between two times,
without using the colon sign (it would be so much faster). 1315 to 1525
would be 130" not 210.

Neat Rob

I just gotta learn to never say never.

Gord

On Sun, 23 Jan 2005 10:05:49 +1300, "Rob van Gelder"
wrote:

Assuming one of your times is in A1

=INT(A1 / 100) * 60 + MOD(A1, 100)

"Rob van Gelder" wrote...
Assuming one of your times is in A1

=INT(A1 / 100) * 60 + MOD(A1, 100)
....

2 function calls for each time value. Can reduce this to 1. Given two times
as integers in A1 and A2, with A1 < A2, the minutes between them could be
calculated as

=(TEXT(A2,"00\:00")-TEXT(A1,"00\:00"))*1440

Of course!
I like it.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Harlan Grove" wrote in message
...
"Rob van Gelder" wrote...
Assuming one of your times is in A1

=INT(A1 / 100) * 60 + MOD(A1, 100)
...

2 function calls for each time value. Can reduce this to 1. Given two
times
as integers in A1 and A2, with A1 < A2, the minutes between them could be
calculated as

=(TEXT(A2,"00\:00")-TEXT(A1,"00\:00"))*1440

Hi, Harlan:

FWIW, since you refer to the number of function calls, I assume your concern
is with speed. Your proposed formula may be shorter, but if speed is the
issue, arithmetic functions generally beat text/string functions (plus
under-the-hood conversion of text to dates/times).

I tried these two formulas, with the numbers 1028 and 1328 in cells A1 and B1:

=INT(B1/100)*60+MOD(B1,100)-(INT(A1 / 100) * 60 + MOD(A1, 100))

and

=(TEXT(B1,"00\:00")-TEXT(A1,"00\:00"))*1440

Charles Williams's FastExcel says the first takes 77 microseconds, the second
105 microseconds.

Myrna


On Sat, 22 Jan 2005 15:16:09 -0800, "Harlan Grove" wrote:

"Rob van Gelder" wrote...
Assuming one of your times is in A1

=INT(A1 / 100) * 60 + MOD(A1, 100)
...

2 function calls for each time value. Can reduce this to 1. Given two times
as integers in A1 and A2, with A1 < A2, the minutes between them could be
calculated as

=(TEXT(A2,"00\:00")-TEXT(A1,"00\:00"))*1440

I don't. See my response <g.

On Sun, 23 Jan 2005 13:26:36 +1300, "Rob van Gelder"
wrote:

Of course!
I like it.

Myrna,

Thanks for following up - it's always good to know these things.

That said, I'd probably use neither and data entry to 00:00 format.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Myrna Larson" wrote in message
...
I don't. See my response <g.

On Sun, 23 Jan 2005 13:26:36 +1300, "Rob van Gelder"
wrote:

Of course!
I like it.

"Myrna Larson" wrote...
FWIW, since you refer to the number of function calls, I assume your
concern is with speed. Your proposed formula may be shorter, but if
speed is the issue, arithmetic functions generally beat text/string
functions (plus under-the-hood conversion of text to dates/times).
....

Good point. Wanna time

=A2-A1-(INT(A2/100)-INT(A1/100))*40

?

That's very clever - 100-60 :)

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Harlan Grove" wrote in message
...
"Myrna Larson" wrote...
FWIW, since you refer to the number of function calls, I assume your
concern is with speed. Your proposed formula may be shorter, but if
speed is the issue, arithmetic functions generally beat text/string
functions (plus under-the-hood conversion of text to dates/times).
...

Good point. Wanna time

=A2-A1-(INT(A2/100)-INT(A1/100))*40

?

I tried these 3 formulas (start time in A1, end time in B1)

=(TEXT(B1,"00\:00")-TEXT(A1,"00\:00"))*1440
=INT(B1/100)*60+MOD(B1,100)-(INT(A1/100)*60+MOD(A1,100))
=B1-A1-(INT(B1/100)-INT(A1/100))*40

The times were 105.6, 77.4, and 76.6 microseconds, respectively.


On Sat, 22 Jan 2005 23:08:37 -0800, "Harlan Grove" wrote:

"Myrna Larson" wrote...
FWIW, since you refer to the number of function calls, I assume your
concern is with speed. Your proposed formula may be shorter, but if
speed is the issue, arithmetic functions generally beat text/string
functions (plus under-the-hood conversion of text to dates/times).
...

Good point. Wanna time

=A2-A1-(INT(A2/100)-INT(A1/100))*40

?

=(TEXT(A2,"00\:00")-TEXT(A1,"00\:00"))*1440 <<

guys you are all lifesaver's... all i can say is that "i" can care less how
many frickin' MICROseconds it takes... the formula prevents me from manually
having to convert any time that crosses the hour mark into a time type
number rather than the numerical number (ie, subtract 40 for 1 hour,subtract
80 for 2 hours, etc...) i tried the [h]:mm formatting thing, but i STILL
had to enter the dang ":" sign... this saves both kelsey1022 and myself the
hassle...


now, if you could do me a favor? explain *why* it works? i'm pretty sure
that the (text(a2,"00\:00") converts the value in cell a2 into a text format
of ##:## {or is it converting a text into a number}, either way, why do you
have the backslash in there?, also what does multiplying the difference by
1440 do? i mean, it works, but how come "1440"?