Small Program to calculate and input Personal leave in an employee schedule

[curiousdesi]

Hi!

Let me start by saying I am very new to complex(to me) excel functions. I am a fast learner though. So I have an employee work schedule I've been working on and so far I managed to get it to calculate how many total hours per shift based on times as well as how many hours each employee gets if they take a holiday and to place 0 if the employee is off. I used the following formula for each day:

=IF(ISTEXT(O12),IF(O12="holiday",LOOKUP(O6,T11:T13,U11:U13),0),(P12-O12)*24-Q12)

Where it takes the input from the hours box and if it's a text it checks to see if it's a holiday and if so it checks a small table where each employee's holiday hours are and returns the correct hours. Otherwise it will either return 0 if it's any text except holiday or it will calculate how many hours based on the times input.

What I would also like to do is add SPD (Personal leave) to the mix but that is giving me issues. I adjusted the formula to the following:

=IF(ISTEXT(O12),IF(O12="holiday",VLOOKUP(O6,T11:U13,2,FALSE),IF(O12="SPD",T16,0)),(P12-O12)*24-Q12)

This portion works well except for 1 issue... I cannot change the SPD hours for only 1 day because as soon as I update the dropdown menu on T16 it changes every instance where SPD is and sometimes an employee needs 4 hours one day and 6 another day and I also have 3 employees to schedule and if I update the SPD hours is changes them all and I really don't want multiple dropdown menus. I originally would have liked a prompt anytime you type SPD otherwise just claculate based on the previous Formula.

I know this is a lot of background but here's where I am right now. I tried creating a command button with the following code but I'm extremely limited in my knowledge of commands in VBA so this is all I was able to do for now. Can someone help me figure out a way to accomplish what I want to do?

Please Login or Register  to view this content.

I would truly appreciate your help in this

[curiousdesi]

Anyone? If I am in the wrong place can someone please direct me to the correct one? I'm trying to get this done as soon as possible..

[skywriter]

You're in the right place.

I would suggest you post a workbook. Sometimes it's not apparent what you want or what the issue you are having is with just a description and some code or formulas. If you do post a workbook and you want a quick answer I suggest you mark it up with notes and perhaps a before and after sheet. Make sure the after sheet data matches the before sheet. The more details the better and the faster you are likely to see a solution.

View Pic

[curiousdesi]

Thank you so much! I will post one soon.

[curiousdesi]

HelpFile.xlsm

Some additional Notes:

The far right hours under Omar one the top week schedule are the most updated ones. Feel free to type in Holiday on the schedule to play with it and see how it works or Off. If you have any questions please let me know and I will answer promptly. I hope the comments help..

[skywriter]

So if I got you an automatic prompt to popup if you type SPD in a cell then you would be ok?

I originally would have liked a prompt anytime you type SPD otherwise just claculate based on the previous Formula.

One thing I don't understand is you are using a formula to pull holidays hours from a table, but it's not going to adjust those hours in the table?

If you want I can have code check each time you type something in column G. If it is holiday or spd I can have an input box pop up immediately asking you the employee name and how many hours you want to add to column j and it will add those to column j in lieu of the formula and I can have it take the hours out of the holiday table or the spd table. How would that work for you?

I could even have it tell you if they don't have enough hours.

How about a sheet that every time you use hours from holiday or spd it enters a log saying employee name type of hours and hours removed.
Maybe the hours showing before deduction.

[curiousdesi]

So if I got you an automatic prompt to popup if you type SPD in a cell then you would be ok?

This I think is exactly what I'm asking for. Any time the scheduler types in SPD it prompts the scheduler to input how many SPD hours for that day and it adds it as the total hours for that day. Eventually I will add Vacation, SPD, and SLP but for now I only need SPD (although if you can add Vacation and SLP that would be AMAZING).



One thing I don't understand is you are using a formula to pull holidays hours from a table, but it's not going to adjust those hours in the table?

In this case no because their holiday hours do not change. All full time employees get 8 hours of holiday pay on holidays like july 4th. It's not like Vacation hours where you only have some available.

If you want I can have code check each time you type something in column G. If it is holiday or spd I can have an input box pop up immediately asking you the employee name and how many hours you want to add to column j and it will add those to column j in lieu of the formula and I can have it take the hours out of the holiday table or the spd table. How would that work for you?


I could even have it tell you if they don't have enough hours.

These hours are only for the purpose of scheduling. The employee requests SPD, VAC, or SLP through payroll and once approved I put the hours in the schedule so I don't know their available vacation hours or SPD hours. This would be amazing if I took care of their available hours but it's not necessary at this point in time but thank you for offering! I am doing this to make it as intuitive and user friendly as possible. So lets say I have a new employee replacing Omar and he's Full time, all I would do is change Omar to Andrew above his hours and change Omar to Andrew in the little table next to it so it knows how many holiday hours he gets. Does that make sense?

How about a sheet that every time you use hours from holiday or spd it enters a log saying employee name type of hours and hours removed.
Maybe the hours showing before deduction.

I hope this answers the questions?

[curiousdesi]

Furthermore whoever is doing the schedule will have a protected version except me because I do not want them to mess with the formulas especially if they don't know what they are doing

[skywriter]

Right click the Sheet1 tab and choose view code.

Paste the code below in the window and test it out by making entries into column G. It should only popup if you type SPD into column G. It is not case sensitive Spd will work, SPD, will work any combination of upper case or lower case.

Please Login or Register  to view this content.

[curiousdesi]

I got a compile error. should End If be End Sub?

[skywriter]

I cut off the code. Leave the end if and put an end sub below it.
Sorry about that.

[curiousdesi]

That's fine thank you. So how can I make it so they select the hours and it places it in total day hours? In this case for Robert it would be Column J. If I can figure out how to do this I need to apply it to all of the appropriate columns. Thank you for the help thus far

[curiousdesi]

I was looking into it and you know how VLOOKUP allows you to select which column you want the resulting info in? Is there something like that but it will allow you to save the value that's typed on the corresponding column of the table?

If so, this would make it really easy to add vacation SPD and SLP. I just don't know what is possible or not

[skywriter]

Boy I'm not much help. I just realized I should have made that code work for SPD entries in Columns G, K and O correct? I can fix that easily.

Just spell it out to me how you want this to happen.

So I'll pick the cell. Someone enters SPD in cell G10 (it's the columns marked START where they will enter SPD?) this would pertain to Kim and the box I made pops up, they acknowledge the box, what do you want to have happen specifically?

[curiousdesi]

Ok so someone types in SPD in G10 for Kim. They get a prompt input box that says something like "Please enter SPD hours for the day" and they type in say 6. It then takes that 6 and inputs that into J10 instead of following the normal formula without destroying the formula. Kind of like I have it in O column where they type SPD and it takes the value from the SPD dropdown menu on T16 and puts it in R10. does that make sense?

[curiousdesi]

Ok so I looked at your code and added the following:

Please Login or Register  to view this content.

Is there a way to make it so it take the value in SPD and puts it in the appropriate Column based on which cell you typed SPD in?

[skywriter]

Okay so remove all the code that I gave you before and replace it with this. Just so you know what happens I typed spd in G10 an input box popped up asking me to enter the spd hours. I entered 6 and hit okay and to the user they see 6.00 in J10. If you look at the formula you will see that it's the same formula with a +6 on the end. So if someone removes the spd and puts a time in G10 the formula will calculate based on the time entered into G10, plus the 6 that I put in.
How does that work for you. Just remember if you are going to copy the formula for a template remove the + 6 from the formula. I fixed the box so it will popup up when spd is entered into columns G, K or O.

If you have other changes don't hesitate to ask. I have issues because I actually enjoy doing this.

Please Login or Register  to view this content.

[skywriter]

I made an error, go into the code and in the second line there are 2 of the word Columns, they should both be Column, just remove the s and it will be fine. They should all 3 be column, so just fix the 2 please.

[curiousdesi]

ok so this almost works perfectly exactly how I wanted it to. I get why just adding the hours to the formula would work but I will have users that will not have access to the formulas since the sheet will be protected. They will not be able to remove the +spdhours and so if the last SPD was 4 hours then when they go back to an 8 hours shift it will show 12 instead of 8. If there isn't a better way to do it I suppose I could get them to retype SPD and type 0 before they go back to regular time but that is tedious and I know for sure some of my less computer literate employees would have issues with it...

[skywriter]

Okay well let me know what you want to do. I thought you wanted the hours added and the formula left intact. It's an input box, whatever is entered by them can be put where you want. You could add a column for spd hours. The hours could go into that column? Hidden column? There are many code driven possibilities, let me know.

[curiousdesi]

I definitely want the formula intact.. I wish I was more knowledgable.. this is why I incorporated the SPD into the formula so that it would be like if it's a text check if it's a holiday and if not calculate the time. but it get's complicated that way and I don't know how to make SPD dependent on the day...

I don't know if it's possible but could the code do something like check what's in the box and follow the formula if time or holiday is involved and do the SPD inputbox if it's SPD?

[skywriter]

I'm not sure what you are asking. It does the input box if it's spd, but leaves the formula and adds the spd hours to the end of the formula. So if it's not spd the formula stays in tact. Do you want spd to just replace the formula with the value put into the input box?

[curiousdesi]

it leaves the formula intact but a user without access to the formula can't take away the SPD that was added.

I am having a hard time explaining... thank you for your help and maybe if I can come up with a better way of explaining I'll try again..

[curiousdesi]

Ok so I got an idea and I'm working on the code. I'll test it and see if it works then post here but it seems a little complicated so maybe if it works someone can help clean the code up a bit?

[Toonies]

Hi try this

BooKTime.xlsm

I have added a few features to your spreadsheet

Please Login or Register  to view this content.

This formula could be possibly shortened by another member with greater knowledge
If you put Holiday or SPD in the Start Time the Cell changes color and The End cell and Lunch cell appear blank.

Input time in the following format

100 = 1:00 AM
1400 = 2:00 PM
2200 = 10:00 PM

have a play around with it

Toonies

[curiousdesi]

Thank you Toonies! this is actually quite cool! I'll keep playing with it while I figure out how to get the right code for what I want to do.. I am getting a type mismatch and I'm trying to figure out if my attempt makes sense or if I'm just being silly...

[Toonies]

What type mismatch are you getting

[curiousdesi]

I have no clue and I know it's going to end up being one of those easy in my face kind of things but as of right now I can't figure it out.

[Toonies]

ok can you post an example of your mismatch or what your after