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

[KELSEY1022]

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.

 

[Rob van Gelder]

Assuming one of your times is in A1

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

 

[Gord Dibben]

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

 

[Gord Dibben]

Neat Rob

I just gotta learn to never say never.

Gord

 

[Harlan Grove]

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

 

[Rob van Gelder]

Of course!
I like it.

 

[Myrna Larson]

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

 

[Myrna Larson]

I don't. See my response <g>.

 

[Rob van Gelder]

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.

 

[Harlan Grove]

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

?

 

[Rob van Gelder]

That's very clever - 100-60

 

[Myrna Larson]

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.

 

[GOD]

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