# Calculate time by pay rate to get the total pay.

1. ## Calculate time by pay rate to get the total pay.

Hello All,

Sorry if this a duplicate post. I need some advice and help, please. I am creating a timesheet. I am using military time to get the hours worked. I have formatted the time in and out as [h]:mm and the total hours worked. Then I used the sum function to get the total hours for the week. Then I create a separate section to take the total hours worked, If a person gets overtime, I have the formula to subtract the total hours from 40 to get the overtime hours. I have formated those cells the same as the total hour's column. Where the trouble is at when the take the Regular hour's and multiply by the pay rate, it doesn't calculate correctly. The same goes for when I calculate the overtime pay. I know where the problem is, its because of excel stores the time different then numbers. I just can not figure out how to solve the issue. I have attached the excel file.

Thanks

.

3. ## Re: Calculate time by pay rate to get the total pay.

The logic is that any cell formatted as time translates to its time value (i.e. X/24, as in X hours out of 24 hours in a day). So to get a numerical value, we need to multiply with 24 first.

=D26*24*10.25

Hope that helps!

4. ## Re: Calculate time by pay rate to get the total pay.

You will be better off multiplying column e by 24, E9:e22, formatted as 0.00,
D26:

5. ## Re: Calculate time by pay rate to get the total pay.

.
ljenkins73

What you now have are three different methods to achieve the same goal.

That is one of the great things about VBA .... there is more than one way to skin a cat.

6. ## Re: Calculate time by pay rate to get the total pay.

Thanks for your help everyone. I got it to work.

7. ## Re: Calculate time by pay rate to get the total pay.

I came across another issue. When my start time is at 4 PM and ends at 12 AM, the hours should be 4, however, the formula gives me -20.00. Not sure how to check for this and then calculate it and also show the hours worked when it is 12 AM to 8 AM. Does anyone have an idea?

8. ## Re: Calculate time by pay rate to get the total pay.

Disregard, I figured it out. Thanks all.

There are currently 1 users browsing this thread. (0 members and 1 guests)