Separating data ( 1 time range) in one column
I have the following cell:
0800-1000 I need to ref 0800 in one column & 1000 in another, to ultimately subtract 0800 from 1000 to get 2 hours. What function would do that ? Thanks, Steve |
Why making it so hard? Why not use sense and use 2 cells and
08:00 and 10:00 then you can just use =B1-A1 assuming B1 holds 10:00 and A1 08:00 of course if you are the receiver of a sheet that comes like that it aint much to do if the times can go after midnight like 1930-0230 =MOD((INT(RIGHT(A1,4)/100)+MOD(RIGHT(A1,4),100)/60)/24-(INT(LEFT(A1,4)/100)+MOD(LEFT(A1,4),100)/60)/24,1) if not =TEXT(RIGHT(A1,4),"00\:00")-TEXT(LEFT(A1,4),"00\:00") format as hh:mm Regards, Peo Sjoblom "Steve" wrote: I have the following cell: 0800-1000 I need to ref 0800 in one column & 1000 in another, to ultimately subtract 0800 from 1000 to get 2 hours. What function would do that ? Thanks, Steve |
Thanks,
Yes, the data was imported into that cell as 0800-1000, so I'll need to use your latter suggestions. Thanks again, Steve "Peo Sjoblom" wrote: Why making it so hard? Why not use sense and use 2 cells and 08:00 and 10:00 then you can just use =B1-A1 assuming B1 holds 10:00 and A1 08:00 of course if you are the receiver of a sheet that comes like that it aint much to do if the times can go after midnight like 1930-0230 =MOD((INT(RIGHT(A1,4)/100)+MOD(RIGHT(A1,4),100)/60)/24-(INT(LEFT(A1,4)/100)+MOD(LEFT(A1,4),100)/60)/24,1) if not =TEXT(RIGHT(A1,4),"00\:00")-TEXT(LEFT(A1,4),"00\:00") format as hh:mm Regards, Peo Sjoblom "Steve" wrote: I have the following cell: 0800-1000 I need to ref 0800 in one column & 1000 in another, to ultimately subtract 0800 from 1000 to get 2 hours. What function would do that ? Thanks, Steve |
All times are GMT +1. The time now is 09:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com