Employee roster for a year

I have 9 employees and they work on a rotation basis. they work for 42 days and are then off for 28 days. I need 5 employees at work on any given day. there can be more than 5 employees at work at some times but never less than 5. how do I make an excel sheet where it gives me an alert for days when there are less than 5 employees at work.

Hi paragatre

Welcome to the forum.

It all depends on when the days on/ days off cycle starts for each employee.

I have prepared a spreadsheet that may help. To input a cycle, go to the relevany cell and press Ctrl+shift+Q

Let me know if it works for you.

Regards
Alastair

Hello Alastair,

Thx a lot. I am going thru it, its not straightaway useable
without customizing, so I am trying with my lil ability to modify it a
bit. But sure that this is the base and direction to go in.

will communicate with u on it further as I am sure i'll need your good
help.

People like u make us feel that there are good things in this world too!


thx again,

Capt Parag Atre.

Hello Alastair,

pls find below, further to our communication so far & Ref to my attachment herewith..


1) Needed the "Excess" column too as we cant afford to have more than five employees at work for too long. So the Column C is modified accordingly.

2) The attached sheet is for yr 2014, so only the necessary entries of yr 2013 have been made.For your referrencing, u can read from 1st jan 2014.

3) Instead of having the macro for a fixed 42/28 days cycle, i need to have a tool where depending on number of work days, the leave will be granted accordingly/pro rata. this is needed as we need to keep adjusting the roster for emergencies & family based requirements, like an extended work tenure or leave. the rate remains the same 42:28.

4) this sheet can be used as the database sheet. However, i need a kind of a bar chart running across a calender which will be more diagramatic and easy for referrence for the employees.

5) Sheet2 is what we r using currently & is made manually. something on these lines but automatic.

6) Once we make a roster in the template u provided, if it gets followed exactly then no problem. But then we dont need to automate it, we can make it manually once a yr without problem. Automation is reqd when we need to extend or cut short someone's work or leave period nad for that the sheet must keep calculating the work done days and leave days availed/accrued on a continuous basis so that adjustments can be made more easily.

7) Thank you so much or ur time! have no words to express the value of charity u r doing!

Hi Parag

My solution consists of having a detail sheet which shows the days when you are over / under and then having a summary sheet.

I was having issues when there was more than one thing happening in the summary "week" so I have shown the different activities on separate lines. I have not shown the unders/overs on the summary (as one would still have to refer to the details sheet to find out which day(s)). This can be added, if required.

To Add a 42/28 day cycle to the Detail sheet press Ctrl+Shift+Q. To transfer the data from Detail to Summary use Ctrl+shift+S.

To see the VBA, press Alt+F8 / Step into

I did try and do away with the month numbers on the summary, but that screwed up all the summary postings. My brain's gone dead so I will put that on the back burner!

Let me know how you get on

Regards
Alastair

hi Alastair,

Thank you very much. I am looking at it and modifying it lil more to suit my needs. will get back to you soon.

brgds,
Capt Parag Atre.

Hi Parag

When I looked at the "month numbers" solution that I tried, the error that I made was so obvious. I had typed 2 when I meant 3 ! How silly. I will amend it when you get back to me.

Regards
Alastair

Hi Alastair,

Sorry for the long gap, was offshore for a few days without access to net. thanks for what u have already done. Attached pls find the modified sheet. have added few columns as I needed the Leave Status to be continuously apparent when I make adjustments to the roster. All looks set as of now, except that would love to have the work and leave duration coming up on top of the respective bars in the Summary sheet, expressed in number of days. So that the employee straightaway knows how many days of leave or work he has just by looking at the roster and doesn't actually need to calculate it.

Brgds,
Capt Parag Atre.

Also pls chk whether my approach in getting the Leave Status is correct or not.

thx.

Hi Parag

I have amended the macro so that:
1 the month numbers do not have to appear in the summary
2 the leave details do not get cleared when the macro is run. (I like the conditionally coloured circles - how do you do that?)

I have looked at your Leave status approach and it seems to be OK. However, I do not know what the rules are regarding special and unspecified leave.

If you would like to show me how you would like the work and leave duration shown, I will see how close I can get to it. I can see that this can be very important, but at the same time, do not want to produce something that is too cluttered or confusing!

Regards
Alastair

Hi Alastair,

Oh I didn't realize that the leave details are disappearing when the macro is used!! actually didn't use the macro after putting in the formula for leave details.
for conditionally coloured "Traffic Lights", go to Conditional Formatting>new rule>Format Style>Icon Sets and then put in ur condition.
One new problem I noticed with this sheet is that the start work dates before January and end work dates after December are shown as 1st Jan and 31st Dec respectively and not the actual dates from the preceding year and the next year. Can this be fixed to reflect the actual tenure dates?? just the date wud do no other details reqd like month or yr, as common sense shud then prevail!
See the first Attachment I had sent which shows how I need the work/leave tenure days to appear on the bars. Attaching a new file is failing. donno if that's coz of the size 102kb?? shudnt be. will try another file. However u can see the first attachment in the thread from my side.
abt special and unspecified leave, there is nothing different really than just that it indicates adjustment. when employees have some commitments back home and need leave or on medical grounds etc then will be adjusting the roster accordingly manually instead of using Ctrl+Shift+Q. These leave days however will be counted on 42/28 cycle pro rata. So the person will work those many days extra after 42.
Whats the purpose of adding column() at the bottom??

Brgds,
Capt Parag Atre.

Hi Parag

I like the traffic lights - I am sure I will use it in the future.

The extra row at the bottom was put in during testing and I forgot to take it out! Now taken out.

I have updated for the length of activity and start date prior to January. I did not have time to work on the time after the year end, but I am sending you what I have so far and will work on this when I have some time (a bit limited, at present!)

Regards
Alastair

Hello Alastair,

That's gr8 job already! thx for ur time so far and yes I completely ustand that u may be having limited time to spend on this right now. will wait, no issues.

Thx for liking something (traffic lights) from my work!! initially I thot u were pulling a fast one on me ;-) . this time when I go back home on leave, I am gonna look for VBA courses and learn all this.

Whenever u do work for the dec end dates, can I request you to add three more employees to the sheet? we have decided to increase our strength. however their work cycles cant be known at this point in time.

thx and brgds,
Capt Parag.

Hi Parag

The overflow to next year proved a little more challenging than I anticipated.

In order to get things to work, I have had to say that blank cells in the daily data will mean that the employee is no longer employed, so unspecified leave becomes "U".

Please note that the start day and end day relate to the work time (as opposed to start of one period and start of the next)

Have fun playing with this, and let me know if I can explain things.

Regards
Alastair

PS

as common sense shud then prevail

It must be nice being an optimist

Hello Alastair,

following things noticed:

1) If a person joins after 1st jan and doesn't have work or leave days starting on 1st jan then his data is not getting transferred to summary. And ideally the new candidates will join only sometime in 2014. Chk employee no 11 for ex.

2) sheet only recognizes W,L,S,U correctly in upper case. Same letters in lower case or any other letters if entered by mistakes then those r being treated as Special Leave on summary sheet. I tried to do a Data Validation using List (referred to col J and cell DD4) but then that only blocks entries other than w,l,s,u. It is not case sensitive.


Brgds,
Capt Parag Atre.

Hi Parag

1) Someone who has not yet started must have the days marked as "-". (This may have an impact on your holiday entitlement calculation)
Blank cells means the employee is deemed to have left.
2) The sheet has been instructed to accept either lower or upper case.

You will note that the system does not handle short periods very well - eg Mushtaq only working to 3 January where there is only 1 cell available so the 42 day period is immediately overwritten by the end date

Let me know if anything else needs adjusting.

Regards
Alastair

Hi Alastair,

the sheet is accepting and counting the small case in the correct category now however the results are not perfect in all cases. for example, if I put Dany's work days from 4th to 6th jan as small w and the rest till 11th as capital w then the summary sheet actually shows the work tenure as 4th to 11th...7 days. The previous work period is getting separated from the day you put the small letter in. Or for that matter if u insert a coupla working days in a midyear work tenure in between, then it shows the tenure start and end date correctly, additionally it shows the small letter days and the length of the tenure is shown as total length minus the coupla days..
I think easier way out of this is that i'll make it a rule to enter only in one case either lower or upper! easy! And i'll add data validation for all the cells where we need to enter w, l, s & u and of course the "-".

Have attached the modified sheet. trust all ok there. will get back if I spot something else.

without suggesting that I am closing the chapter here, u have been of great help! cant express enough.

Brgds,
Capt Parag Atre.

Hi Parag

I had not foreseen the possibility of mixing upper and lower case in any one activity! This is now dealt with.

DAFA did not get reported due to lack of "-" on 1 Jan.

I am not particularly pleased with the reporting of start / duration / end potentially in one cell (where these happen within one "week"). I suggest that I re-write this bit so that start/end are on 1 line and duration on another. However, this has the potential to take quite some time - so if you are not particularly bothered, I will not proceed.

Let me know!

Regards
Alastair

Why is it that jobs that might take a long time only take a few minutes?

Herewith version 9

Regards
Alastair

Dear Alastair,

I think finally we r done!

the sheet 2 that u added in ur last attachment with all the colours and numbers, does that need to be there or it was part of some experiments u were doing and can now be deleted?

Thanks a ton Alastair, u'll be my guest if u visit the UAE or India! pls lemme know.

Brgds,
Capt Parag Atre.

Hi Parag

Yes you are quite right - just part of testing. Feel free to delete.

Glad it is now working as required, but do let me know if anything else is needed.

I will take you up on your kind offer of being your guest. (I have never been to UAE or India but you never know!)

Regards
Alastair

dear Alastair,

Thx once again. Pls feel free to be my guest. And yes will let u know if any changes r reqd.

Brgds,
Capt Parag Atre.

Hi Parag

Despite my comment in #5, I believe it is quite important to flag up on the summary sheet those times when you drop below your 5 required, so I have added this facility.

Regards
Alastair

Hello Alastair,

Sorry for the delayed response. Just saw ur version 10. came on my 28 day leave to India, have been down with soar throat, fever etc. what u have done in 10 is appreciated, however ur comments in #5 withstand. The summary sheet is only to hand it over to the employees for a graphic representation of the whole year. As far as the planning, making and adjustment to the roster is concerned, that is done at the office level who will be using the daily detail sheet for that. Secondly, when there is a condition where there are less than 5 employees at work, the office will adjust the roster to make it compliant and hence nothing will show up anyway on the summary sheet. So all in all we will stick to ver 9 for now. I have attached herewith our version 9 with some small modifications like data validation, protecting the daily detail sheet, freezing of panes, hiding of columns, etc. pls keep this one on record with you and use this one if u wish to improvise further.

Thx & Brgds,
Capt Parag Atre.

That's fine. I hope Dany gets a special prize for getting his leave allocation exactly right!

regards
Alastair