+ Reply to Thread
Results 1 to 8 of 8

Calculate time by pay rate to get the total pay.

  1. #1
    Registered User
    Join Date
    04-19-2020
    Location
    Clarksburg, WV
    MS-Off Ver
    MS Office 365
    Posts
    7

    Post 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
    Attached Files Attached Files
    Last edited by ljenkins73; 04-21-2020 at 08:58 AM.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    5,927

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

    .
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-04-2020
    Location
    Surprise, AZ
    MS-Off Ver
    2010
    Posts
    8

    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. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,194

    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.
    Ben Van Johnson

  5. #5
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    5,927

    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. #6
    Registered User
    Join Date
    04-19-2020
    Location
    Clarksburg, WV
    MS-Off Ver
    MS Office 365
    Posts
    7

    Thumbs up Re: Calculate time by pay rate to get the total pay.

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

  7. #7
    Registered User
    Join Date
    04-19-2020
    Location
    Clarksburg, WV
    MS-Off Ver
    MS Office 365
    Posts
    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. #8
    Registered User
    Join Date
    04-19-2020
    Location
    Clarksburg, WV
    MS-Off Ver
    MS Office 365
    Posts
    7

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

    Disregard, I figured it out. Thanks all.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 05-16-2014, 10:10 AM
  2. how to work out rate based on total time and total amount
    By lisaathers in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-09-2013, 06:44 AM
  3. How do I calculate total hours by rate of pay?
    By FrankF in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM
  4. How do I calculate total hours by rate of pay?
    By FrankF in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 PM
  5. How do I calculate total hours by rate of pay?
    By FrankF in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  6. How do I calculate total hours by rate of pay?
    By Aladin Akyurek in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 06:05 AM
  7. How do I calculate total hours by rate of pay?
    By FrankF in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-16-2005, 07:05 PM

Tags for this Thread

Bookmarks

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