how-to block ads
|
neonhomer
Honoray Mythbuster
Premium
join:2004-01-27
Edgewater, FL
clubs:
 ·Earthlink Cable Mo..
| [Excel] Figuring overtime and assign a value Okay, I have two questions about Excel. I am making a time sheet for my work, so I can just fill it out, print it to a PDF, and then send it to my office. I have most of it worked out, but I have run into two snags...
1> After 40 hours, I need to go to overtime. I have a column for normal hours, and then a column for overtime hours. When the total of the column reaches 40, it needs to start putting the hours in the OT column. Often, the OT happens in the middle of the day, so it would have to take a day of time, and then split it over two columns.
2> I get paid for my driving by a zone. Zone 1, 2, 3, 4. I want to be able to assign a value to when I put in Zone 3D, it silently figures 60.00, and then adds up the totals of the zone column.
I have the sheet created, and can post a copy if anyone needs to look at it to see what I am talking about.
--
"F is for Fire that burns down the whole town...
U is for Uranium...... Bombs...
N is for NO SURVIVORS!!!!!" Sheldon Plankton | |
|  
PapaDos
Cum Grano Salis
Premium,MVM
join:2001-02-08
Lasalle, QC | Re: [Excel] Figuring overtime and assign a value It's always easier to help with a sample file...
--
Festina Lente | |
| | | 
Grey Area
join:2003-11-18
UK | Hi neon.
OK, looked at your form. Other than adding up your overtime hours, I'm not sure what you want it to do...?
Also need a bit more clarification on the Zones idea... | |
| |
neonhomer
Honoray Mythbuster
Premium
join:2004-01-27
Edgewater, FL
clubs:
·Earthlink Cable Mo..
| Re: [Excel] Figuring overtime and assign a value The zones are....
1
2R
2D
3R
3D
4R
4D
Each one needs to be assigned a value. So when I put in "3D", the formula thinks $60.00, and will add it up in the background. I don't want to see the amount, just the 3D (or whatever the zone is). This total needs to be shown at the bottom of the zone list, and in the "Zone Total" box at the top.
So if I put in a 3D and 4D, that would total $150.00 (3D=$60, 4D=$90). I want $150 to show in cell J30, and in H8. (H8 just has a "=J30" in it).
Then if I add another 3D, the total should now go to $210. I don't want to see the individual values, though.
--
"F is for Fire that burns down the whole town...
U is for Uranium...... Bombs...
N is for NO SURVIVORS!!!!!" Sheldon Plankton | |
| | | 
tao
Chaos Impends
Premium
join:2000-12-03
Lansing, MI
·Comcast
| Re: [Excel] Figuring overtime and assign a value To simply this, you should use military time. So one day of work becomes 700 to 1600, or 9 hours. Then the OT equals the number of hours worked minus 8.
If you must have the presentation you show, then do your calculations on a second sheet and link the values.
Further, I see a bug there in the hours, if this is signed and all that, the hours total maybe should not be 43 hours. As you see at the top, the total hours is now 46, I corrected this in the linked file.
Anyway, my 2 cents...
--
I play darts: »www.triple20dartingsupplies.com | »www.adaoflansing.com
I support unions: »www.1099alldrivers.org www.1039atu.com
| |
| | | |
tao
Chaos Impends
Premium
join:2000-12-03
Lansing, MI
·Comcast
| Re: [Excel] Figuring overtime and assign a value Played with that sheet a bit more and got the zones working with a compromise. The cells for the zones were split into two columns, with the right being for zone 4 and the left for zone 3, then the cells having numerical values was counted and a function was added to the top list to handle those numbers.
But like I wrote in the previous post, if you need the sheet to have all the features you showed in your example, then simply place the calculations on sheet B and link them to A.
--
I play darts: »www.triple20dartingsupplies.com | »www.adaoflansing.com
I support unions: »www.1099alldrivers.org www.1039atu.com
| |
| | |
PapaDos
Cum Grano Salis
Premium,MVM
join:2001-02-08
Lasalle, QC
1 edit | In J30, enter this:
=SUMPRODUCT( ($J$15:$J$29={"1","2R","2D","3R","3D","4R","4D"}) * {0,0,0,0,60,0,90} )
Adjust the second vector values according to your needs...
--
Festina Lente | |
|
Grey Area
join:2003-11-18
UK
2 edits | *edit* added a bit more an uploaded an example. You can keep your time formatting if instead of calculating "hours worked -8" you calculate "hours worked -(8/24)" - this is because Excel defaults durations to days as decimal values, so you need to subtract "one third of a day" rather than "eight hours"
Please note my example has column J hidden.
Neon:
Insert a column before column K on your example. In the new cells J15 to J29 type the formula
=IF(K15="3D",60,IF(K15="4D",90,0))
You can add more "IF" statements for the rest of your codes. In J30 just sum the cells above it, then hide column J. This has the advantage of not changing the appearance of your form.
Using the formula as given will leave the cell as zero if you enter a code that you don't have a specific value for. | |
| | |
neonhomer
Honoray Mythbuster
Premium
join:2004-01-27
Edgewater, FL
clubs: | Re: [Excel] Figuring overtime and assign a value ???? I have a friend/co-worker who is interested in this worksheet as well, so he doesn't have to fax in a paper copy each week. Just fill it out, print as a PDF, and email it over.... | |
| | |
PapaDos
Cum Grano Salis
Premium,MVM
join:2001-02-08
Lasalle, QC | Re: [Excel] Figuring overtime and assign a value Look at the MAX() and MIN() functions...
--
Festina Lente | |
| | |
neonhomer
Honoray Mythbuster
Premium
join:2004-01-27
Edgewater, FL
clubs:
·Earthlink Cable Mo..
| Re: [Excel] Figuring overtime and assign a value The =MAX(cell1:cell2) only returns the highest number in a series of #'s..
I need it to actually limit the number in the column.
So if I work 10 hours that day, I need it to limit to 8, and put the other two in the OT column next to it.
--
"F is for Fire that burns down the whole town...
U is for Uranium...... Bombs...
N is for NO SURVIVORS!!!!!" Sheldon Plankton | |
| | | |
PapaDos
Cum Grano Salis
Premium,MVM
join:2001-02-08
Lasalle, QC
1 edit | Re: [Excel] Figuring overtime and assign a value Just max( YOUR_TIME, 8 ) in one cell and min( YOUR_TIME - 8, 0 ) in the OT cell...
--
Festina Lente | |
| | | | | 
tr6scott
join:2002-03-23
Oxford, MI | Re: [Excel] Figuring overtime and assign a value I learn so much from PapaDos.... | |
| | | | | | | | |
|
·