Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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) |
#5
|
|||
|
|||
"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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
"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 ? |
#11
|
|||
|
|||
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 ? |
#12
|
|||
|
|||
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 ? |
#13
|
|||
|
|||
=(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"? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i calculate the difference in time? | Excel Worksheet Functions | |||
calculate difference in time to hours | Excel Worksheet Functions | |||
How do I calculate difference in days & hours between two dates e. | Excel Worksheet Functions | |||
calculate negative or positve difference in time | Excel Discussion (Misc queries) | |||
How do you calculate the difference between two values within a p. | Excel Discussion (Misc queries) |