[Solved] Determine pay, given rate and minutes worked

Discuss the spreadsheet application

[Solved] Determine pay, given rate and minutes worked

Postby downtownjb5 » Fri May 08, 2020 11:10 pm

(SOLVED)Hello. Can anyone help me with a formula i need. Here's what I'm doing.
i need the cell to give me the sum of .28 x ? (? being a certain number of minutes) = __________.
Example: job pays $17/hr so the .28 is what $17/hr pays per minute. so if someone works 3 hrs and 40 minutes, i need a formula that would give me what that 40 minutes total is so I can add it to the total hours and come up with a TOTAL pay . Hopefully that makes sense.
Last edited by robleyd on Sat May 09, 2020 7:48 am, edited 3 times in total.
Reason: Tagged [Solved]
OpenOffice 4.1.6
Windows 10
downtownjb5
 
Posts: 4
Joined: Fri May 08, 2020 11:00 pm

Re: Help with formula

Postby Zizi64 » Fri May 08, 2020 11:24 pm

Please upload your ODF type sample file here. We must see the data types and the structures in your file. Modify the sensitive contents to some dummy data in a copy of your file.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9542
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Help with formula

Postby FJCC » Fri May 08, 2020 11:31 pm

The best way to handle this, I think, is to enter the hours and minutes in a single cell. For example, select cell A1 and then choose the menu Format -> Cells. On the Numbers tab, set the Category to Time and set the Format to the one with the Format Code HH:MM. On my system this is the first entry in the Format list and it is displayed as 13:37. Now enter 3:40 in A1. In B1 enter 17. In C1 enter
Code: Select all   Expand viewCollapse view
=A1 * B1 * 24

C1 will display 62.333. The reason for multiplying by 24 is that time is stored in units of Days, so to convert from Days to Hours I multiplied by 24.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7748
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Help with formula

Postby RusselB » Fri May 08, 2020 11:40 pm

Where did you get your .28 from?
$17/hr = 28 1/3 cents per minute. Close to your .28, but not exactly the same.
When calculating a pay, someone paying attention will notice the difference between $17/hr for 3 hours 40 minutes ($62.33) and $0.28/minute ($61.60)
Calculating a total pay knowing the amount of time and the pay rate is just a simple multiplication. Due to the fact that spreadsheets use time as fractions of a day, your formula would be something like
Code: Select all   Expand viewCollapse view
=A1*17*24
where A1 is the time (eg 3:40)
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6108
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Help with formula

Postby downtownjb5 » Fri May 08, 2020 11:56 pm

Wow, thats great. You're right, I should have given you all the info I had. My idea is to be able to set up a simple spreadsheet for people to fill in their info and then the total just works out . bottom line is they make $17/hr and then i have to take off 20% for taxes and then add mileage paid at .54 cents per mile so the toal I owe them is ?. Thats what i need the spreadsheet to tell me. they would come in and fill in how many hours they worked and how many miles they drove and it would spit out the total I owe. Phew. is that possible.
OpenOffice 4.1.6
Windows 10
downtownjb5
 
Posts: 4
Joined: Fri May 08, 2020 11:00 pm

Re: Help with formula

Postby downtownjb5 » Fri May 08, 2020 11:59 pm

thanks to all who are helping me.
OpenOffice 4.1.6
Windows 10
downtownjb5
 
Posts: 4
Joined: Fri May 08, 2020 11:00 pm

Re: Determine pay, given rate and minutes worked

Postby RusselB » Sat May 09, 2020 4:58 am

You already have a formula for calculating the time.
Multiply the result of that formula by .2 to get the 20% for taxes
You'll need another entry for the mileage, then multiply that by .54 (I hope that's supposed to be 54 cents, not .54 cents/mile)
If you have the time in column B and mileage in column C, the total you are asking for can be in column D using
Code: Select all   Expand viewCollapse view
=B2*17*24-current()*.2+C2*.54

I suspect, that, most of the time, this will not return a number that is proper for dollars & cents (ie: rounded to 2 decimal places)
The rounding can be done by wrapping the entire code using the ROUND function. This would be my preference, but some people/systems want more detail than 2 decimal places, as there will end up being a rounding discrepancy.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6108
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Determine pay, given rate and minutes worked

Postby downtownjb5 » Sat May 09, 2020 7:05 am

THANK YOU TO ALL....
OpenOffice 4.1.6
Windows 10
downtownjb5
 
Posts: 4
Joined: Fri May 08, 2020 11:00 pm


Return to Calc

Who is online

Users browsing this forum: Bill and 13 guests