Conditional Formulas?

[tj007]

I have been really struggling with a project at work. It is a time sheet. For the most part, with the great assistance of Blue Dragon2, I have captured 90% of the scheduled work shifts. There is one scenario that is wild but allowed by administration. A person leaves early one day for a doctors appointment. The same person will either come in early the next day or some other day in the week to make up for the time lost, or they make work late on another day to compensate for the time.

PROBLEM: The time sheet is design to look for 40 hours in 8 hour shifts with a 30 minute lunch break each day. Once it calculate 8 hours in a day, any time beyond the 8 hours is pushed to the overtime premium cell. Hence the problem with people working beyond 8 hours to make up a day for which they worked less than eight. The time populates the overtime cell instead of the cell with less than eight hours. Is there any hope for this situation?

 

[xlbo]

depending on layout, could you not check the other days in the week for that employee to see if any are < 8..... still wouldn't help if the early day was in a different week to the late day....other than that, could you have some kinda marker to show it was an early day ???

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[tj007]

xlbo, do you have any suggestions about markers? I am open to any suggestions.

 

[SkipVought]

Hi,

Your problem is that you designed for a subset of the requirements rather than from the top down.

There must be some rule(s) regarding work hours in a week. Can hours be carried over from one week to the next? Is the rule that hours over 40 in a week are deisgnated as OT/Premium Hrs? What about someone who works 4 - 10:00 days or 3 - 13:20 The whole ball of wax has to be addressed, otherwise, you are going to be PATCHING your design when each, &quot;oh, by the way...&quot; comes up.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[nberryman]

I agree with Skip, was Blue Dragon aware of this when he answered your posts.

Switch off the PC and sit down with a pen and paper and write out exactly what you want and all the rules.

Once you have it all straight you should be able to sort it out, Patching is never a good idea as I have found out in the recent past.



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[SkipVought]

Neil,

I can tell the voice of experience. tj will consume MUCH LESS time doing what you suggest than by trying to accomodate this, &quot;oh BTW&quot; and all the others that are bound to follow.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tj007]

Skip, hours can not be carried from one week to next. But, a lot of hours are flexed as I described above. Hours over 40 are OT/Premium. However, working more than eight hours in one day can be flexed to the day with less than eight. This sounds like a job for VB scripts.

Yes we have two people that work 10:00 hour days

 

[tj007]

nberryman

That is exactly what I did with the accounting section. It is evident they did not include every factor. So, I will ask them to write down all the rules, or they can do it the old fashion way. Thanks to all.

 

[SkipVought]

tj,

As Neil advised, &quot;Switch off the PC and sit down with a pen and paper and write out exactly what you want and all the rules.&quot;

Then you can decide if it's something that can be accomplished with spreadsheet functions or it it will require VBA programming.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tj007]

Skip

Will do, Again, thanks to all of you for your advise.

 

[bluedragon2]

Tj,

Skip and Geoff are correct in what they were saying. I wish you could have contracted the project out to me. With all of the BTW's I could be rich

One suggestion since I know the basics of your spreadsheet. Why don't you put in an area called other. Instead of trying to account for each instance, put the information into the other area and let it add to the total time worked for the week with an explantion. So if you have someone making up time, have them enter the time there with an explantion and the hours are added to the total. You can also put conditions in it to allow for your overtime guidelines and such.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[tj007]

Blue

The spreadsheet works fine with the exception of flex time. I will try what you suggested. If you wish to become a vendor, let me know. I will show you how.

 

[jdhilljr]

Is this set up like the one we helped with before?
If so I can alter the formula to include a comparison using counta*8 and if they are short it can calculate upto that total. therefore taking that into account the only problem you will have is on holiday weeks where some companies pay overtime for 32 hours worked.
Let me know.
Jim

 

[tj007]

jd

Yes, this is the same set up. When it was rolled out after so called testing. Accounting came with a oh BTW lots of people use flextime.

As previously stated,
PROBLEM: The time sheet is design to look for 40 hours in 8 hour shifts with a 30 minute lunch break each day. Once it calculate 8 hours in a day, any time beyond the 8 hours is pushed to the overtime premium cell. Hence the problem with people working beyond 8 hours to make up a day for which they worked less than eight. The time populates the overtime cell instead of the cell with less than eight hours. I have seen people leaving early and reporting to work early to make up time. I have also seen people leaving early and working late to make up time.

 

[jdhilljr]

Can you email one worksheet that is setup with the error along with a list of any other problems you are having with it?

jim.hill@procollectortips.com

Jim

 

[tj007]

Jim, its on the way

tj