|
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 -- |
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 -- |
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 ) |
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 |
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 |
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 ) |
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 ) |
| All times are GMT +1. The time now is 08:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com