Keying in hours and minutes

[alan green (wheelhouse)]

Is there any way of keying in hours and minutes into a cell e.g. 1hr 45 mins
could look like this 1.45. The only thing I was thinking is that Excel would
presume that this is a decimal point and therefore the figure after the
decimal point would be classed up-to 100 instead 60 (minutes).

The reason I would like to do this is we have a time management system that
runs in hours and minutes and so I'd like to be able to key in the time
allocated on a job and then the time taken and then the next column would
show efficient we have been (percentage wise) on each job.

Any help would be much appreciated.

 

[JE McGimpsey]

Is there any way of keying in hours and minutes into a cell e.g. 1hr 45 mins
could look like this 1.45. The only thing I was thinking is that Excel would
presume that this is a decimal point and therefore the figure after the
decimal point would be classed up-to 100 instead 60 (minutes).

The reason I would like to do this is we have a time management system that
runs in hours and minutes and so I'd like to be able to key in the time
allocated on a job and then the time taken and then the next column would
show efficient we have been (percentage wise) on each job.

Any help would be much appreciated.

Three suggestions:

1) Use times keyed in as 1:45 instead of 1.45, and enter your allocated
time as an XL time. Then just divide your time taken by your time
allocated to get efficiency:

A B C
1 Allocated Taken Efficiency
2 2:00 1:45 =B2/A2 ==> 0.875

This is by far the simplest method.

2) Design your formulas to interpret the decimal values as hours and
minutes:

C2: =((INT(B2)+MOD(B2,1)*10/6)/24)/((INT(A2)+MOD(A2,1)*10/6)/24)

which can, of course, be simplified to

C2: =(0.6*INT(B2)+MOD(B2,1))/(0.6*INT(A2)+MOD(A2,1))

3) Use the suggestions here:

http://www.mcgimpsey.com/excel/easiertimeentry.html

 

[alan green (wheelhouse)]

This works an absolute treat - thanks very much for your help