Help with Excel

[Throwmeabone]

I'm making a timesheet to keep track of how many hours several employees work over a week and calculate their pay.

I made columns for in, out, and time worked for each day (all formatted as time-the 2nd option down). I then have a column that gives the total time for the week.

What I can't figure out is how to take that number for total time worked, for example 20:15, and multiply it by the numerical hourly rate to get the amount of pay. The reason it doesn't work to simply multiply them is because Excel sees 20:15 and translates it to 20.25/24.

I hope that makes sense.. Thanks.

EDIT: I suck at math. In case anyone wants to know, you simply multiply the time x rate x 24.

 

[jersiq]

You have to convert to Base24
Assume C13 has the total hours worked for the employee.
In the cell you want to put the wages the formula would be:

(C13*24)*hourly rate of pay

Edit: I suck at reading comprehension, disregard my post.

 

[KLin]

1. Calculate hours and minutes separately

I.E.

HoursWorked = 20:15
Pay = (Rate*Hour(HoursWorked))+(Rate*(Minute(HoursWorked)/.6/100)

The hour() and Minute() functions will return each respective value. Dividing by .6 then by 100 converts the minutes to the decimal equivalent. I'm not sure why you're multiplying by 24.

 

[jersiq]

It's in the KB from MS
KB article it's under time to number conversion

 

[Throwmeabone]

Originally posted by: KLin
1. Calculate hours and minutes separately

I.E.

HoursWorked = 20:15
Pay = (Rate*Hour(HoursWorked))+(Rate*(Minute(HoursWorked)/.6/100)

The hour() and Minute() functions will return each respective value. Dividing by .6 then by 100 converts the minutes to the decimal equivalent. I'm not sure why you're multiplying by 24.

Times in Excel are really decimals. So if an "hours worked" cell shows for example "8:00", to Excel it's really 8/24 or 0.33. You can see this if you format the cell as a number with 2 decimal places. Multiplying by 24 cancels that out and allows "8:00" to be equivalent to 8 for calculations.

 

[KLin]

Originally posted by: Throwmeabone

Originally posted by: KLin
1. Calculate hours and minutes separately

I.E.

HoursWorked = 20:15
Pay = (Rate*Hour(HoursWorked))+(Rate*(Minute(HoursWorked)/.6/100)

The hour() and Minute() functions will return each respective value. Dividing by .6 then by 100 converts the minutes to the decimal equivalent. I'm not sure why you're multiplying by 24.

Times in Excel are really decimals. So if an "hours worked" cell shows for example "8:00", to Excel it's really 8/24 or 0.33. You can see this if you format the cell as a number with 2 decimal places. Multiplying by 24 cancels that out and allows "8:00" to be equivalent to 8 for calculations.

using the functions I mentioned above does the same thing. You're getting the integer value of the timepart.

If A1 = 8.5 and A2 = 20:15...

Using "=(A1*HOUR(A2))+(A1*(MINUTE(A2)/0.6/100))" as the formula it comes up with 172.125, or 172.13. Of course there's more than one way to skin a cat. 😛