Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculate difference (mins) between 2 Times
Hi guys,
Re-table below in cols A to C where its required to calculate the "Diff (mins)" in col C (Col C = Time B - Time A) Times are in text in cols A & B in the form: 0245 hrs, 0315 hrs, etc Time A Time B Diff (mins) 0245 hrs 0315 hrs 30 0705 hrs 0735 hrs 30 1955 hrs 2050 hrs 55 2335 hrs 0005 hrs 30 2355 hrs 0035 hrs 40 etc What can I put in C2, and copy down to return the desired results ? Thanks -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#2
|
|||
|
|||
Hi
=TIME(LEFT(B2,2),MID(B2,3,2),0)-TIME(LEFT(A2,2),MID(A2,3,2),0)+(TIME(LEFT(B2,2),MI D(B2,3,2),0)<TIME(LEFT(A2,2),MID(A2,3,2),0)) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Max" wrote in message ... Hi guys, Re-table below in cols A to C where its required to calculate the "Diff (mins)" in col C (Col C = Time B - Time A) Times are in text in cols A & B in the form: 0245 hrs, 0315 hrs, etc Time A Time B Diff (mins) 0245 hrs 0315 hrs 30 0705 hrs 0735 hrs 30 1955 hrs 2050 hrs 55 2335 hrs 0005 hrs 30 2355 hrs 0035 hrs 40 etc What can I put in C2, and copy down to return the desired results ? Thanks -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#3
|
|||
|
|||
Thanks, Arvi !
(I multiplied the results returned by 24*60 to convert it to numbers) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Arvi Laanemets" wrote in message ... Hi =TIME(LEFT(B2,2),MID(B2,3,2),0)-TIME(LEFT(A2,2),MID(A2,3,2),0)+(TIME(LEFT(B2 ,2),MID(B2,3,2),0)<TIME(LEFT(A2,2),MID(A2,3,2),0)) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) |
#4
|
|||
|
|||
Max wrote:
Hi guys, Re-table below in cols A to C where its required to calculate the "Diff (mins)" in col C (Col C = Time B - Time A) Times are in text in cols A & B in the form: 0245 hrs, 0315 hrs, etc Time A Time B Diff (mins) 0245 hrs 0315 hrs 30 0705 hrs 0735 hrs 30 1955 hrs 2050 hrs 55 2335 hrs 0005 hrs 30 2355 hrs 0035 hrs 40 etc What can I put in C2, and copy down to return the desired results ? Thanks -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- Another way =(MOD((INT(LEFT(B2,4)/100)+MOD(LEFT(B2,4),100)/60)/24-(INT(LEFT(A2,4)/100)+MOD(LEFT(A2,4),100)/60)/24,1)*1440) Regards, Peo Sjoblom |
#5
|
|||
|
|||
Works great, Peo !
Thanks .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Peo Sjoblom" wrote in message ... Another way =(MOD((INT(LEFT(B2,4)/100)+MOD(LEFT(B2,4),100)/60)/24-(INT(LEFT(A2,4)/100)+M OD(LEFT(A2,4),100)/60)/24,1)*1440) Regards, Peo Sjoblom |
#6
|
|||
|
|||
Hi
The another option was to format the cell with my formula as "[m]" or "[m]:ss" -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Max" wrote in message ... Thanks, Arvi ! (I multiplied the results returned by 24*60 to convert it to numbers) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Arvi Laanemets" wrote in message ... Hi =TIME(LEFT(B2,2),MID(B2,3,2),0)-TIME(LEFT(A2,2),MID(A2,3,2),0)+(TIME(LEFT(B2 ,2),MID(B2,3,2),0)<TIME(LEFT(A2,2),MID(A2,3,2),0)) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) |
#7
|
|||
|
|||
Noted with thanks, Arvi !
I went for the "simple" numbers conversion as there would be downstream calcs in due course -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Arvi Laanemets" wrote in message ... Hi The another option was to format the cell with my formula as "[m]" or "[m]:ss" -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate duration between two dates and times in excel? | Excel Discussion (Misc queries) | |||
Calculate the difference two times | Excel Discussion (Misc queries) | |||
Calculate the difference between two times | Excel Worksheet Functions | |||
How can I calculate the difference between times without using th. | Excel Worksheet Functions | |||
calculate negative or positve difference in time | Excel Discussion (Misc queries) |