usafts Posted August 14, 2009 Posted August 14, 2009 (edited) Hello and thanks for your help. I am attempting to create a timesheet for the office. I have the layout done and everything works as needed but I would like some of the input to be automatic. Can someone help with a calculation? I have a couple of hidden fields and a few calculation fields: - One is "Regular Hours" and the other is "Overtime". I would like the "Regular hours" field to show actual hours worked up to 8 and the "Overtime" hours field to show the overtime hours. This is based upon the total in a hidden calculation field that is fed by three sets of "Time" fields. I just read this and I hope it makes sense. I will try and upload some code if necessary. Thanks again. Edited August 14, 2009 by Guest spelling
comment Posted August 14, 2009 Posted August 14, 2009 Regular Hours = Min ( TotalHours ; 8 ) Overtime = Max ( TotalHours - 8 ; 0 )
usafts Posted August 14, 2009 Author Posted August 14, 2009 Thanks for such a quick response. Please forgive my rookie brain. I see what you are telling me and it makes sense but I am not sure how that needs to be added to my calculation.
usafts Posted August 14, 2009 Author Posted August 14, 2009 Thank You for the help! After looking at it....puting it away for awhile and then looking at it again, your very simple help saved the day. It works perfectly! Thanks again!
usafts Posted August 14, 2009 Author Posted August 14, 2009 OK....CRAP! It works perfectly BUT...it populates the field with "8", even if it is a day off. I only want it to populpate if it is a work day. Is there a way to keep it blank unless a specific field is populated? Thanks again.
comment Posted August 14, 2009 Posted August 14, 2009 It will NOT return "8" unless the TotalHours field contains a value of 8 or higher (I assume by "it" you mean the RegularHours field). Perhaps you meant "0"? If you make it a calculation field, and do NOT change the default 'Do not evaluate if all referenced fields are empty', it will remain empty when TotalHours is empty. You can also format the field to display only non-zero values.
usafts Posted August 14, 2009 Author Posted August 14, 2009 (edited) Thanks for your patience. Yes, I was referring to the RegularHours field. It is a calculation field and the default 'Do not evaluate if all referenced fields are empty' is used...yet although the total hours field is empty, the RegularHours field returns "8" using "Min ( TotalHours = 8 )". I will try the non-zero values. The OvertimeHours field works correctly. I am confused. Edited August 14, 2009 by Guest
usafts Posted August 14, 2009 Author Posted August 14, 2009 Problem Solved! The problem was....there was an idiot at the keyboard. It WAS the 'non-zero value' setting that needed to be adjsted. Everything works correctly and I thank you for both your skills and your patience. B
comment Posted August 14, 2009 Posted August 14, 2009 OK, now I am confused - because I just tested this and it appears you were right the first time: it does evaluate even when TotalHours is empty. I don't think it should behave like this: although Min(), like all aggregate functions, is supposed to ignore any empty values, still - the calc itself shouldn't evaluate when the only referenced field is empty. Anyway, try one of the following patches: Min ( TotalHours + 0 ; 8 ) This will return 0 when TotalHours is empty or 0. Case ( TotalHours ; Min ( TotalHours ; 8 ) ) This will return empty when TotalHours is empty or 0.
usafts Posted August 14, 2009 Author Posted August 14, 2009 I came back to relate that my last post was premature. I had the same problem again. I will try your next suggestion.
usafts Posted August 14, 2009 Author Posted August 14, 2009 That was it! Definately this time. It all works as required. Thank you so much for the help and extra effort. B
usafts Posted August 18, 2009 Author Posted August 18, 2009 Hello and thanks for your help. Last week I put together a time-sheet solution that keeps track of all of my employees time. Regular and Overtime hours are automatically calculated based upon time fields, thanks to help from some of you. MY NEW PROBLEM is...Our work week runs from Monday to Sunday. If one of my people works an extra day, the hours are calculated as regular hours when they should be all overtime. [color:red]- Can a script be written that counts the number of days worked within a specific period and if >5 returns 0 in a field that would normally have to show 8? I hope my long question makes sense. Thanks.
usafts Posted August 18, 2009 Author Posted August 18, 2009 Anyone? Am I asking my question clearly enough or am I in left field? Any ideas will be greatly appreciated.
JesseSFR Posted August 19, 2009 Posted August 19, 2009 What is your ultimate goal usafts? Are you basically trying to get a total number of "regular hours" worked in a week and then a total of the "overtime hours"? If so I would find all the time sheet entries for a worker in a week and then total the hours. If the total time is greater then 40 just take the difference of the total and 40 and you have their overtime hours. Does this solve your problem?
usafts Posted August 19, 2009 Author Posted August 19, 2009 Jesse, Thanks for taking the time! Let me see if I can ask my question clearly. Forgive me but I am a noob and a half. What I have is 3 sets of time fields "IN" "OUT", "IN2" "OUT2", "IN3" and "OUT3" Those six fields are ultimately added together and return a number to the "ATOT" field. The field "ATOT1" looks at the total in the "ATOT" field and returns the total, but only up to 8. This is because anything over 8 is overtime and will be returned to a different field. [color:red]OK....THE PROBLEM: Our work week is from Monday to Sunday and everything works perfectly as long as my guys only work 5 days in a work week. If somebody works a sixth day, in the same work week, the total in "ATOT1" returns 8...as it should, based upon the code. BUT those 8 hours are all actually overtime hours. [color:red]THE QUESTION: [color:red]Is there a way for a script to count the number of days worked in a work week...and anything >5 would return a "0" to the "ATOT1" field although it already has a calculation applied to it? Does that make sense? Thanks again
comment Posted August 19, 2009 Posted August 19, 2009 If I understand correctly, you need to count the number of previous days worked in the same week - NOT just the number of days worked in a week. For this, you will need a calculation field that returns a common value for all dates in the same week - let's call it cWeek (result is Date) = WorkDate - DayOfWeek ( WorkDate - 1 ) + 1 Next, define a self-join relationship of the Timesheets table as: Timesheets::cWeek = Timesheets 2::cWeek AND Timesheets::WorkDate > Timesheets 2::WorkDate Now you can use the expression: Count ( Timesheets 2::WorkDate ) > 5 to determine whether the record is the sixth (or higher) worked day in its week. Note that this assumes every record is a day, and that there is at most one record per day.
usafts Posted August 19, 2009 Author Posted August 19, 2009 Thanks comment, No. In my inexperience, my layout may be part of the problem. Each record represents one full pay period for one employee. It works as needed eccept for the "6th day issue". Thanks
comment Posted August 19, 2009 Posted August 19, 2009 What exactly is a "full pay period"? Earlier you said: What I have is 3 sets of time fields "IN" "OUT", "IN2" "OUT2", "IN3" and "OUT3" I'm having a hard time understanding how someone can come in and out three times, and still put in 6 days of work in a week - unless the pay period is LESS than a week. Please provide a full explanation of the business rules.
usafts Posted August 19, 2009 Author Posted August 19, 2009 I'm having a hard time understanding how someone can come in and out three times, and still put in 6 days of work in a week - Comment - Frequently an employee will work 6 days in a given week because of manpower shortages. Also employees may work various hours within a day or will come back later in the day, after an 8 hour shift to cover another employee who is sick or had a medical appointment etc. ex: 08:00 - 16:00, 20:00 - 24:00 Also, some of the employees work a 9 hour day with a 1 hour lunch. These employees are required to sign out for lunch and then back in after lunch. ex: 15:00 - 19:00, 20:00 - 24:00 If one of these employees comes back for coverage, all three sets of fields will be used. ex: 08:00 - 12:00, 13:00 - 17:00, 20:00 - 2400 Did I explain that correctly? I do have the file but I am not sure how to best get it to you. Thanks.
comment Posted August 19, 2009 Posted August 19, 2009 That would make each record represent one day of work - but you said it doesn't, so I don't get it.
usafts Posted August 19, 2009 Author Posted August 19, 2009 I'm sorry. I guess I am not explaining it correctly. I would be glad to send you the file. It would be much easier to understand than my weak attempt at describing it. Is there an email address I can send it to? I would rather not post our business files in the open forum.
usafts Posted August 23, 2009 Author Posted August 23, 2009 It was suggested that I create a dummy file that I could post here in the forum so that you may better see my issue in its' natural habitat. I did a couple of layouts and just removed company info but my problem is the same with both files. If my files are usefull to anyone, please feel free to use them....If not......I won't be too surprised. :-) Thanks, in advance for your help! B PAYROLL_EXAMPLE_2.fp7.zip PAYROLL_EXAMPLE.fp7.zip
JesseSFR Posted August 23, 2009 Posted August 23, 2009 (edited) I created a field called week which is simply weekofyear( Date1 ) and created a relationship to all the previous days worked in that week. Then I added logic to your OT1 and ATOT calcs to deal with 6th or 7th days appropriately. I think this solves your problem. PAYROLL_EXAMPLE_with_new_week_calc.fp7.zip Edited August 24, 2009 by Guest Err... third time's the charm
usafts Posted August 24, 2009 Author Posted August 24, 2009 JesseSFR, Thanks for taking the time! I am encouraged that you feel that my problem is solved but I am embarrassed to tell you that I am not sure how to apply your information. B
JesseSFR Posted August 24, 2009 Posted August 24, 2009 For some reason, possibility related to my own stupidity or the fact that I was watching football, I did not post the file in my last post. I added it. Sorry for the confusion.
Lee Smith Posted August 24, 2009 Posted August 24, 2009 What is the Password and userID of your files
usafts Posted August 24, 2009 Author Posted August 24, 2009 Jesse, IT WORKS! Christmas in August. Thank you so much for the help and the sense of humor. One of the companies my employer owns is beginning to transition to FM and I imagine there will be a need for your services when the IT people start battling with the conversion. I WILL let them know how to get ahold of you when that service is needed. Thanks again! B
usafts Posted August 25, 2009 Author Posted August 25, 2009 Lee, Thanks for trying. I just went and looked and realized that the first file was set to open incorrectly. They should both open with full acces now. Jesse got the #2 file working for me. If you have the time to look at #1, that would be great. They both do the same thing but they are laid out differently. One is, "One recored for one day" and the other is "One recored for the entire pay period". I dont think the fix for #2 will work for #1 because of the individual records. Thanks again. B
usafts Posted August 25, 2009 Author Posted August 25, 2009 MORON FOLLOW-UP QUESTION Jesse, Lee and any other Guru's, I think that in my ignorance, I have managed to put the cart before the horse. This solution works perfectly if I have only 1 employee. I am not seeing how to include 14 more people in the mix. Is my layout out in left field? Should I revert to the other layout? Is suicide the only rational solution to my problem? :-) Thanks for all the help. B
usafts Posted August 26, 2009 Author Posted August 26, 2009 Hello all, After looking at the modified file and creating records with different "work dates", I am realizing that it does, in fact, not work after all. I will try and ask my question again and I will post the full-access file. If anyone has the time to look at it, I would be forever in your debt. What I tried to do was put together a timesheet that would allow each employee to input his/her own times. Our work week runs from Monday to Sunday and frequently an employee will work a 6 day week. I am hoping to be able to count the number of days worked within the same work-week so that if an employee works a 6th or 7th day, those hours are returned to the "overtime" field and not the "regular hours" field. Thanks again. (The example file shows two six-day weeks - and you can see that it does not return the correct information) PAYROLL_EXAMPLE_with_new_week_calc.fp7.zip
Ocean West Posted August 26, 2009 Posted August 26, 2009 (edited) This is the sample file that comes with fmp - is this something that would work? Time_Cards.fp7.zip Edited August 26, 2009 by Guest
usafts Posted August 26, 2009 Author Posted August 26, 2009 Mr. D, Thanks for the file. I will have a look and see if I can mold it a little. Thanks again. B
usafts Posted August 27, 2009 Author Posted August 27, 2009 Ocean West, I just spent HOURS trying to modify that file to meet my needs. I am rapidly realizing that I am fighting a losing battle. I DID learn a lot while playing with the calculations but I am in over my head. I was able to get it to take care of two weeks and not just one at a time bit it still only allows for one set of begin and end times. I also need it to recognize a specific pay period. (ours runs from the 11th to the 24th and the 25th to the 10th). Don't ask me why, because I just don't know. I think I am going to go back to plan "A" and wish for the best. Thanks for the file. B
usafts Posted August 27, 2009 Author Posted August 27, 2009 Hello FM crew, This experience has stopped being enjoyable...moved into frustrating...and is now bordering on maddening. Each time I get a suggestion or a file from someone, I get all fired up and excited because of the great ideas. Sadly, each is a different shovel with which I continue to make my hole deeper. I have posted my original file. This is the closest layout to what I need as well as the layout my employees are used to using. There are a couple of things I need it to recognize: 1. Pay periods are 11th thru 24th and 25th thru 10th each month. 2. Work weeks run Monday to Sunday 2a. 6th & 7th days of same work week must be overtime and not regular hours. 3. Hours broken down by day and not week If anyone has the time and energy to evaluate this layout, it would be an amazing help. Thank you! B PAYROLL_EXAMPLE.fp7.zip
Recommended Posts
This topic is 5626 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now