• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with extracting data from staff leave calendar and listing it into another sheet.

K

Kanu Kohli

New Member
Hello Guys n Gals,:)

this is my first post, I need some help into extracting data from my staff leave calendar and then listing the data into a basic table in another sheet. This data I need extracting are the dates that staff members have taken time off during the year, whether its a holiday, being sick or for training. I have a summary sheet showing their allowance and what days are being used for either a holiday, sick or training, and they are put into their own categories as staff members mark the dates they want using a Key. H = Holiday, T - training, S - Sick, \ - Half day.

I want to be able to have the same thing but this time just to list the actual dates of when staff are absent. I've uploaded a file with some data to show you. I would appreciate any help for this as I've hit a wall lol.

Thanks
Kanu
 

Attachments

  • Leave Tracker.xlsm
    53.5 KB · Views: 15
Q: Why do You use 'a calendar' from 'left to right'?
If You could use it other way then it would be 'easier' with further years.
There are 'more' rows than columns.
 
This was calendar was not made by me, I have recently joined a new role and I've been asked to maintain this spreadsheet. Any Idea's on how I can solve my original question?
 
Hi,

Thanks for the reply, Yes this is good, but I need it to show all dates for the whole of the year, i.e. from Jan-Dec for each staff member. Also I'm guessing that to show the actual date its just a case of changing the format? instead of showing 29 it shows 29/6/16 etc.

Kanu
 
Hi ,

The workbook has the calendar starting from June 2016 ; instead of the range starting from column FA , we can start from column B. Is that what you want ?

Regarding the dates , we will need to have all the dates in a particular row , so that we can index into them ; this will not be a problem.

Narayan
 
Hi

The months can be changed via the slider along the bottom of the calendar, each of the staffs holidays are marked for each month this way. This is the only copy of the tracker I have and don't have any basic version available.

Regarding the dates. as long as they can be listed in a separate sheet that links it to the relevant staff names its fine. e.g.

Employee name Time off type
- Staff member 1 1/5/16, 2/5/16 holiday etc

really appreciate your time in helping me
Kanu
 
@Kanu Kohli
If You are interesting with this kind of calendar, without 'some formulas'.
I could add Your needed informations to a new sheet.
With this, You will have a calendar ready until end of 2030,
You just select Year or Month.
You could also have groups of staff and so on ...
 

Attachments

  • CALENDAR.xlsb
    37.2 KB · Views: 3
Hi,

@NARAYANK991

Yes this is correct only problem now is that the actual calendar is broken and the conditional formation is broken. all the days, weekend & bank holidays have been removed. also there is nothing marked against employee 1, when they have used the majority of there holidays as well as some of the dates not matching. but other than this its good. much appreciated thanks


@vletm

Hi,

sounds good, could you please make one using the info from the first attached sheet that I added. Also could you add the relevant bank holidays as well as a summary tab like the one found in my sheet as my boss likes to keep track this way. much appreciated thanks

Kanu
 
Hi ,

I have not modified or deleted or moved or in any way tampered with the existing formulae , cells , CF rules ,...

Can you specify at least one cell where this change has taken place ?

Narayan
 
Hi @NARAYANK991

the sheet you attached above showing the recent changes you had made, if you go to the page 'leave tracker' you will see all of the days and formatting rules not in place, as well as the slider used to change the months. something must have been accidentally altered?

kanu
upload_2016-7-19_14-31-8.png
 
Hi ,

I have no idea what you are talking about ; the slider works , the holidays are shown in red , there is blue and gray also being shown. All of these are because of the CF rules in place.

Mention at least one cell , give the cell address where you think the CF rule is not working.

See the attached file for the corrected results ; there was a mistake in the formula due to which Employee 1 was not being shown the holidays taken by him / her.

Narayan
 

Attachments

  • Leave Tracker.xlsm
    66.4 KB · Views: 4
@NARAYANK991 apologies please forgive me, the new sheet is now working fine for me thank you very much for your help. I think the other sheet was corrupted probably during download process. however I seem to be getting "#" appearing above the weekdays. please see screenshot below. also some of the dates are still not matching.

for example employee 8 in June has one holiday marked for the 15th Weds but in the summary tab it shows up as the 18th which is a Saturday. and the same happens again for the one day in July. again on calendar its marked as 11th Monday and on summary tab is shown as 15th Friday? Is that an error on my part or is that something wrong with the formula?

Kanu

upload_2016-7-19_14-50-11.png
 
@Kanu Kohli
I added few more sample features;
try to fill Your marks and check Your bank holidays.
Some of Your needs takes time to make ...
Those are possible to do if You really want and start to use this.
 

Attachments

  • CALENDAR.xlsb
    38.9 KB · Views: 5
@vletm

Hi,

looks good, but what are the green bars? bank holidays? Also how can I check them as their are no other sheets open to see if they are correct. Also Id like to point out that these should be UK bank holidays apologies for not mentioning earlier.

My boss has put me in charge of this so he said im free to change make a new one/alter etc as long as he can see which employees are absent and that it matches the above criteria etc.

@NARAYANK991 Please don't find this to be offensive to the help you have given me, I'm just trying to find a solution.

I appreciate the help from both of you guys. :DD

Kanu
 
@NARAYANK991 Awesome! Thanks so much for your help. However I have one more small request. Would it be possible to say which of the dates are either a Holiday, Sick, etc. if this could be added then the tracker would become perfect. I really appreciate your work on this.

Kanu
 
@Kanu Kohli
Green; Those are holidays from that list You have there. Color can always change. Why compare? Did You mean to have only dates instead of bars?
Of course somewhere would be text which holiday. Later, there could be any years holidays and there could be more information if needed.
'...he can see which employees are absent and that it matches the above criteria etc. ' ... that would make many ways ...
As I wrote, If You will use this kind of version, then I will continue ...
 
@vletm Yes please continue, I just wanted to be able to check if the bank holidays were correct but all sheet tabs are hidden and as im using latest office im still getting use to it as my personal version is 2013 at home. but please continue I would like to see what you could come up with. and even if I do not end up using it, I can always send it out to other departments for them to be able to use.

Again @NARAYANK991 please don't take this the wrong way with me asking for his help also.
 
@Kanu Kohli
Next version...
Many colors can modify by 'superuser' as well as many more setting
I add sample of summary ...
Do it need to be annual or 'previous' years values? now monthly.
Ideas ...
 

Attachments

  • CALENDAR.xlsb
    44.1 KB · Views: 5
@NARAYANK991 Thanks so much for your help. Really appreciate it! :DD

@vletm Looks good so far, however the summary tab needs a bit more, in that it should show the staffs allowance for the year throughout no matter which month i'm in and also show how much they have taken against their allowance. Again it mostly needs to be able to do similar stuff as in the sheet i uploaded. It needs to be able to have the whole year and so i can select choose any month. All Pages should be accessible for superuser to update details for staff etc. I don't need previous years as we normally reuse the same tracker and just update the year and bank holidays.

Also after using your example for a bit, it seems to be very slow at adding in holidays, its having to think about. also it gives an error when i try to type in whether its a holiday or sick leave, is that the way you have designed it or is that something on my end?

Thanks again
Kanu
 
@Kanu Kohli
'Summary Sheet': I needed to know which period You need;
okay, traditional one calendar year... and other values can show too.
Do this file has only one user (superuser) or many users?
Normally, only 'calendar view' should update and other sheets could see by selection.
'I don't need previous years as we normally reuse the same tracker and just update the year and bank holidays.' hmm... That means, You will delete previous years data 1st of January ... amazing! Why cannot use same file longer?
Or, if You don't delete that file and You need for some reason to see something from previous year ... then You have to find previous years file - open it and find Your needed information. ... or just check it from same file?
'Slow': now it's slow because it solves those absent days and
like adding holidays is planned to use 'drop down' which makes sure that You will use 'correct absent marks'. And I need to know what really needs, after that it's possible to make it quicker - so far sample!
My boss has put me in charge of this so he said im free to change make a new one/alter etc as long as he can see which employees are absent and that it matches the above criteria etc.
 
@vletm the current calendar is on a shared server in which any of my team member can go in and mark their own holidays etc. However i will be in charge of making sure everyone uses the sheet correctly so it is done properly. Previous years issue - that is the way is was dealt with before i joined, now if you can add this function to it so from when its comes to booking holidays for 2017, I will be able to check and see 2016s holidays and with it still being stored there, that would be helpful. However I will still need to be able to update bank holidays and staff details incase of new employees join/leave.

I need Summary tab to be like the one in my original sheet as my boss finds this helpful, also I need it to have show the actual dates of the taken holidays/sick/training/half days etc in a separate sheet against staff names. See the latest version from Narayank991 on summary sheet. those are the only requirements that are a must.

Thanks
Kanu
 
You must log in or register to reply here.
Back
Top