First we need to do some formatting of the cells and then kind of remember to
ignore anything that might show up in the formula bar at the top of the
window later on.
Choose your labor hours cells/column and use Format | Cells and go to Custom
formatting and type in this as the format to use:
[h]:mm
Now when you enter 41:18 it will remain displayed as 41 hours and 18
minutes.
Next thing to understand is how Excel stores time values: as fractions of
days. And since 1 hour is 1/24 of a day, then we need to multiply the 40:18
value by 24 to get a real number to divide by, so your formula becomes
=A3/(A2*24)
and make sure that the cell that is in is formatted as General (or numeric
other than date or time) to display 1.4... instead of something unexpected
like 11:24 (which would happen if Excel automatically tries to convert the
results of the formula into time format instead of General).
"ClarkeTeam" wrote:
> Thanks -- however, I believe my problem is in the formatting of the labor
> hours column. When I type in the hours -- 40:00, 33:47, etc. -- Excel
> automatically formats it in date format.
>
> For example, I typed in 41:18 for 41 hours, 18 minutes. Excel automatically
> formats it to "1/1/1900 5:18:00 PM". I have tried to do a custom format and
> have not been able to get away from this.
>
> When I do my formula, I believe this format is not allowing the correct
> calculation. I thought this would be an easy thing to fix, but an hour of
> searching Excel help and online help is getting me nowhere. Thanks for any
> help you can offer!
>
> "JLatham" wrote:
>
> > Believe it or not, you wrote the formula.
> > =A3/A2
> > is the formula to put into any cell other than A3 or A2 to get the result.
> >
> > If this is early in the game you might want to consider laying out your data
> > in a different fashion - to make these calculations a little easier.
> > Consider 4 columns:
> > A B C D
> > 1 Date Widgets Labor Hours Widgets/Hour
> > 2 7/1/07 60 40:00 =B2/C2
> > 3 7/2/07 75 60:30 =B3/C3
> >
> > What makes that layout easier for the novice is that the formulas in column
> > D can easily be 'filled' on down the sheet as more dates are added. Easier
> > than copying from say A4 down to A7 for a paste, then from A7 into A10, etc.
> >
> > But you may have different needs. By the way - if you put that =A3/A2
> > formula into A4 and later copy it and paste it into A7, it will then read
> > =A6/A5 - Excel does that type of work for you automatically during a copy and
> > paste (or a fill) operation.
> >
> >
> > "ClarkeTeam" wrote:
> >
> > > My spreadsheet contains a row of hours worked by our team per day -- 31:06,
> > > 18:32, 43:44. I also have a column of number of widgets produced that day.
> > >
> > > How can I get Excel to calculate the number of widgets produced per hour?
> > > For example, row A is 7/1/07 and we had 40:00 labor hours that day (A2) and
> > > 60 widgets produced that day (A3). I need a calculation that says =A3/A2 and
> > > the result is 1.5 widgets per hour.
|
Similar Threads
