# 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  Register To Reply

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

. ``Please Login or Register  to view this content.``  Register To Reply

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!  Register To Reply

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, ``Please Login or Register  to view this content.``
D26: ``Please Login or Register  to view this content.``  Register To Reply

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.   Register To Reply

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

Thanks for your help everyone. I got it to work.  Register To Reply

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?  Register To Reply

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

Disregard, I figured it out. Thanks all.  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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

#### Tags for this Thread #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1