# Time calculation..

Posted by Steve on January 12, 2001 12:33 AM

Here is my problem: In column 6 of my spreadsheet I enter a time-in/time-out value (eg: 900-930) to represent the time I spent at a job. I need to have the sheet put the time spent into column 23 for further calculations. Column 23 must be represented in minutes. I am at a loss on how to code this. Help anyone??

Thanks, Steve

Posted by Dave Hawley on January 12, 2001 1:21 AM

Hi Steve

No code needed just format the cells as Custom: [m]

Dave

Posted by Aladin Akyurek on January 12, 2001 2:22 AM

Just to satisfy the curiosity: given the representation, one can use assuming an entry in A1:

=RIGHT(A1,FIND("-",A1)-1)-LEFT(A1,FIND("-",A1)-1)

Posted by Steve on January 12, 2001 7:41 AM

Thanks for the reply(s) but I think I may have worded my problem incorrectly. Let's say for example I enter 900-1030 into cell F4. This means I started the job at 9:00am and finished at 10:30am. I need to have the amount of time spent, in this case 90 minutes automatically placed in cell W4. My preference would to have the number entered as minutes, not hours. The whole purpose behind this is to monitor performance (time wise) and to establish an average hourly pay scale since each job is paid by the job, not by the hour.

Thanks, Steve

Posted by Aladin Akyurek on January 12, 2001 9:17 AM

If you enter your data consistently, then use

=((MID(A1,6,2)-MID(A1,1,2))*60)+(MID(a1,8,2)-MID(A1,3,2))

Thus enter 0930, never 930. 3-digits cases can be also covered, but that would make the above formula a bit longer.