Timesheet issue - Adding half a day to vacation, sick or lwop 1

[YNOTU]

Trying to think out loud... need your ideas please


Hello again all, I'm looking for ideas on how this could be implemented but am drawing a blank.


Here's the scenario, I have a timesheet calculating the start-date, out-to-lunch, back-from-lunch, end-of-day


Allow me to explain what I'm trying to do:
columns L10 - L25 contain the following formula:

=IF(I10<H10,I10+1-H10,I10-H10)+IF(K10<J10,K10+1-K10,K10-J10)

Which in turn, displays 8:00 hours worked for each day from L10 - L25

I also have drop-down menus for each day in case an employee want to assign that day for whatever the drop-down reads. Notice that L11 has been assigned "sick leave" (this is reflected at the bottom on L30


Here's my problem: I'm being told that employees could allocate hours to sick, lwop or vacation. (as in this example)


What I'm thinking is I need some radio buttons that when clicked, it would display the remaining time from a day and a description to either vacation, sick or LWOP...

 

[YNOTU]

for some reason my image is not showing, here's the link to it.

Thanks

http://img459.imageshack.us/my.php?image=timesheetno3.jpg

 

[SkipVought]

Tony,

Give us some help!

I assume that you are calculating bi-monthly time allocated time.

"Here's my problem: I'm being told that employees could allocate hours to sick, lwop or vacation. (as in this example)"

So exactly WHAT is the problem? That statement tell me nothing about a problem. It's just a statement of fact. Why does this fact present a problem?

I can assume lots of problems, but maybe not, because I don't know your system.

Please be CLEAR, CONCISE and COMPLETE.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[YNOTU]

My apologies for not being clear on this SkipVought. I obviously rushed my question.


Here's the issue

As you can see, it's a simple sheet calculating days and time or worked per day.

You will also notice some drop-downs boxes which are used to display and caluculate (at the bottom) time taken off for vacation, sick, etc, etc... per day.

My problem is that I only took in consideration that a whole day would be assigned to vacation, sick, etc, etc...

If you noticed the example for L11 (Friday 17th) Mike only worked 6 hours. He needs the ability to assign the remaining 2 hours to vacation, sick or lwop. If he assigns the day to sick leave, the sheet then assumes the entire day was sick leave.

As I begin to think the process through, I keep going back to needing perhaps radio buttons as follows:

º Vacation
º Sick Leave
º LWOP

and when either is selected, it would display the remaining time per day.

Much appreciate your time SkipVought. Hope this is a little bit clearer.

SkipVought, You have been a pillar in this community for a very long time now. I remember seeing you back when there were only 200,000+ members and I used to use a different name. (but forgot my password) I originally signed up in 2000 but went away for a long time due to my job, now that I don't primarily do this any more, I find myself trying to help in the office again.

The more things change, the more they stay the same I guess.

 

[SkipVought]

Is the sheet merely to calculate an employee's time for the period (throw-away data), or is it, along with that, a means to enter data that will be stored, analysed and reported?

It seems that the data structure (thinking in terms of the LATTER) ought to be...
[tt]
EmplID WrkDate TimeIN TimeOut TimeCode
[/tt]
For a Work Date, you can have multiple rows. Not to worry!

Using a table in this format, the information you are requiring can be determined. But you have to think in terms of data storage vs. a data report.



Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[YNOTU]

Our HR department currently hands out by-monthly "paper" timesheets, then collects them and enters all data into whatever program they use to store, analyze and report.

Within the next year we're going to be moving into automating this process to work within our Intranet (not built yet). Ideally, and since I already have this form, I'd like to integrate it with SQL now that way we're ahead of the curve.

Is this what you're talking about?

 

[PeterMoran]

Hello HelloTony,

What you need ultimately is Total Hours in the Period broken up by let us say Activity (Leave, Work etc). And on any one day there can be more than one activity from your example. Then the simplest approach - what would happen on a paper system - is that two lines would be used where there were two activities for the one day.

I suspect your spreadsheet will not fit to this requirement very easily without significant modifications, but it does seem to be a basic requirement.

You could add an extra column to the right which was only used when there was a breakup required for the day with the Sick Leave able to be entered in this new column.

Good Luck!

Peter Moran

 

[YNOTU]

I can manage any additional cosmetic changes with no problem or adding basic code to it.

My formula on L10 - L25 is

=IF(I10<H10,I10+1-H10,I10-H10)+IF(K10<J10,K10+1-K10,K10-J10)

My thinking was, adding a radio button (if possible) which would then take the above formula and calculate the time a user would assign to sick, vacation or lwop for that day.
Since the formula already calculates all times per day...

 

[yogia]

HelloTony:

As has already been pointed out especially by Skip that your data layout ought to be optimized. Having said that, please have a look at the following modification to your existing layout ...

As you can see I have used V, S, and L as in-line fields rather than as option buttons


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[YNOTU]

I think that'S the best way to go with instead of radio buttons, as of matter of fact, I already made the cosmetic changes.

Now, I have added the three new columns and my "total hours per day" formula is now on O10 - O25 which is here

=IF(I10<H10,I10+1-H10,I10-H10)+IF(K10<J10,K10+1-K10,K10-J10)

I've tried adding L10, M10 & N10 to O10 to calculate it but keep getting ######. Also what format should I make L, M & N10 in order for the calculations to work.

 

[yogia]

HelloTony:

The formulas I used in the setup that I played with are shown in the following ...

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[YNOTU]

Thank you yogia. I was able to modify the sheet enough to get it working as per your instructions.

I've asked the appropriate dept. to review all changes. My checking went good.

Thanks again.






Thanks
- Tony

 

[yogia]

HelloTony:

Great Job in getting every thing sorted out the way you wanted ... now let us keep EXCELing.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[YNOTU]

Absolutely yogia, let's keep Exceling indeed. I'm hoping all steps taken comeback mistake-free if I missed something, I'll be back....






Thanks
- Tony