I am working on a time sheet. The guys get a minimum of 56 hours every week they they work (7 day weeks) but we don't work every day so for example if we work 3 days, they get a 24 hour minimum. I'm trying to figure out how to work out an if then type line where if they have less than an 8 hour average for the days they work the total is adjusted to the 8 hour average. Any suggestions?
Hi, welcome to the forum
Try this...
=AVERAGEIF(J5:J18,"<>")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
That's a great start thank you very much. I think i might should have worded the question a bit differently and an average formula may nkt be exactly what im looking for. The other thing i need to do there is make it able to adjust the value up to the minimum. For example on that first page named alberto on the days 6/14 through 6/19 he worked 41.5 hours. I need that number bumped up to 48 hours. Thank you for the quick reply.Originally Posted by FDibbins
Hi, welcome to the forum
Try this...
=AVERAGEIF(J5:J18,"<>")
OK, something like this?
=MAX(SUM(J5:J18),COUNTA(J13:J18)*8)
That is exactly what I needed. Thank you very much. Ive had a few classes in highschool and college that taught excel, and used it alot in physics and chemistry classes, but never had to deal with anything like this. I've got a lot more to learn than i realized.
Happy to help (and no, you have a LOT to learn lol)
A question for you...
how will you determine which rows to use if you have 4 weeks worth of data in your table?
I will only have 2 weeks at a time in the table (payday is every 2 weeks). I will be using that formula twice, once for rows 5 through 11 and again for 12 through 18 and totaling them for the final hours the check is for. Thats the way the hours are figured, minimum 8 hour a day average ending each week. Unless of course there is a better way to write the formula.
Last edited by jbroberson21; 06-26-2016 at 01:07 AM.
OK, if it will only be for 2 weeks, then probably not worth putting something together to look at dates
I have another small problem sometime, but only sometimes, our "days" dont end until the early hours of the next morning but i would like to keep the work day together on the day it started on to keep it simpler to look at. The problem is, with the formula the file had when i downloaded it, it doesnt figure the hours right. The formula they used is in column k. Its (e#-d#)+(h#-g#) with # being the row number. Is there a way to write something for that with out the PM start time to AM end time messing it up? *EDIT*. After making this post i found this (h#-g#+(h#<g#)) to fix the second half of the existing formula. Is there a better way of doing that, or should that work well?OK, if it will only be for 2 weeks, then probably not worth putting something together to look at dates
Last edited by jbroberson21; 06-26-2016 at 01:35 AM.
You will probably only need the 2nd part of this, but I did it for both parts...
=MOD(E5-D5,1)+MOD(H5-G5,1)
Answered before I even found my less convenient option that I edited to add to the previous post. Thanks again for all the help.
Happy to help
If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)
Thanks for the rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks