MrExcel.com - Your One Stop for Excel Tips & Solutions

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

  • OzGrid Business Applications

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)

Aladin

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.

Aladin

Posted by Steve on January 12, 2001 11:22 PM

:

Thanks Aladin...

This sort of works for what I need but still presents a couple of problems. First, it doesn't handle the transition of morning to afternoon. Example: 1130-0130 gives a value of -600. Secondly, the format I am required to submit for pay is not to use army time. The consistant 4 digit entry is ok for me but might confuse the data entry clerks that read it. They are creatures of habit as you may already know.

Steve