• 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.

NARAYANK991 said:
Hi ,

See the attached file.

Narayan
Click to expand...

Hi @NARAYANK991

Sorry to be a pain, but is it possible to put the Dates in a vertical column on a completely separate sheet, with Employee names and Type(holiday,sick etc) & Dates as column headers, with the following dates put into corresponding columns. My boss wants it like this as he needs to be able to feed it into another system. and that system can only read basic raw data. again sorry to be a pain, I'd really appreciate your time and effort into helping thus far.

Thanks
Kanu
 
@Kanu Kohli
Previous- current - further years;
There is already that feature ready! reply #9 (just select year and continue using)
as well for bank holidays, just add information.
With previous version, You have to set Sheet Tabs visible manually.
With further versions, it could be automatic for superuser and for everybody else disabled. Many possibilities...
Summary-tab; If You want to have show the actual dates then there have to be year calendar! ... and if needed a list of dates with value of absent in comment.
As well this Summary-tab would be only visible for boss ...
Q: Do 'the half day' use half day of the absent days? hmmm...
if mark only one 'half day' in whole year, do it use one whole absent day or
if mark two 'half days', do it use one or two absent days?
 
@vletm half day is counted as 0.5. currently its marked down as 1 absent day I think but in the formula its shown as 0.5. if you go onto the summary page of my sheet and look under the "Remaining" & "taken" tabs it shows how the formula works with regards to that.

Summary Tab - thats fine as long as it shows the actual dates of all employees is absent along with type of absence, this is needs to be shown in a completely separate sheet as raw data ( no formating etc) , as my boss will need to use this data to feed into another system.
 
Kanu Kohli said:
Hi @NARAYANK991

Sorry to be a pain, but is it possible to put the Dates in a vertical column on a completely separate sheet, with Employee names and Type(holiday,sick etc) & Dates as column headers, with the following dates put into corresponding columns. My boss wants it like this as he needs to be able to feed it into another system. and that system can only read basic raw data. again sorry to be a pain, I'd really appreciate your time and effort into helping thus far.

Thanks
Kanu
Click to expand...
Hi ,

Please upload the kind of layout you want on a separate tab. I am not able to understand how you want the data displayed.

Narayan
 
@Kanu Kohli
Next sample version: including Summary.
... if 30 names and everyone would have 22 dates to show ... ?

There are 'menu'-sample in left top corners (light green) to select other sheets.
Few things are still missing ...
and there are rules how to work with this.
 

Attachments

  • CALENDAR.xlsb
    57.3 KB · Views: 3
@vletm Looks really good, I like the interface for it very eay to grasp. Holiday allowance vary between staff members as some have been with company for longer then they will get more allowance. This in my version was controlled by allowing us to be able to enter the allowance in the summary tab. this was easy to manage as we could type in a new amount and all calculations would be done accordingly.

Rules would be helpful, that way I can learn how to use it properly and then show other staff members on how to use the sheet. I could then also put a separate sheet with rules, as shown in my example, of how to do certain things within the new calendar.
 
@vletm
So far so good. The job list will need to be available to be changed as there are different roles within my department. Or can this be done already if so how can i change this? Other than that it looking very good. again thanks for helping :)
 
@Kanu Kohli
Go to 'Lists':
Roles = Group; modify those as needed
Names; Fill UNIQUE 'KOC ID#'and name, select 'GROUP & fill 'Allowance' as needed
Holidays; fill 'Holiday' and 'Date' as needed
You can modify colors of some items by modify named cells colors.
... coming later: saving of these settings, automatic backups...
>> Ideas, questions? <<
 
@Kanu Kohli
+ saving settings and backup-version.
Next You need to test this, record login time for me.
Do also same things with 'superuser' and 'the boss' and send the file back.
I'll try to do those soft 'user' permits.
So, only 'You', 'superuser' and 'boss' would open this file in this test period!
>> Ideas, Questions? <<
 

Attachments

  • CALENDAR.xlsb
    92.2 KB · Views: 9
@vletm

Calendar looks brilliant, everything works and does its job, testing won't be possible as our department is ridiculously busy during work hours. Ill try and get something on this but cant promise this.

One request is that can you provide label on the menu button and anything else you have missed as well as providing instructions on how to use this within the sheet as the whole team will have access to this as they will be in charge of imputing their own holidays, sick etc. Whilst myself and boss will maintain and make sure everything is being followed correctly. again thanks so much, really brilliant calendar! :D
 
@Kanu Kohli
As I wrote:
Next You need to test this, record login time for me.
Do also same things with 'superuser' and 'the boss' and send the file back.
I'll try to do those soft 'user' permits.
So, only 'You', 'superuser' and 'boss' would open this file in this test period!
>'The menu button' could use only above mentioned users.
That's why I wanted to test and send file back.
Also reread reply #37, there were basic instructions for basic settings.
Do not make new row or delete row, those frames should be always same.
>For normal use:
1) select year & month as needed
2) activate cell where need to make mark
3) use drop down
4) press <Enter>
5) repeat steps 1.. 4 as many times as need.
>That file makes backup while every opening to backup-folder,
if there'll be any challenges to make folder by code, then do it manually.
 
@NARAYANK991

Hi

Can you have a look at the attached sheet, as there are some dates that have not been listed and its causing an issue within the new program that the data is being used for.

The problem is happening on the 'Dates' tab, where I asked for your help in listing all employee dates. however for Employee 1, there are 3 dates missing from there December holiday. Could you please amend this as i've tried doing it and I keep breaking it. If you go onto the summary Tab and scroll across to Employee 1 you will see dates 23/12/16, 29/12/16, 30/12/16 missing from the dates tab.

Also just to be on safe side could you also make sure that If anyone adds new dates for holiday, sick etc. that its automatically added to the 'Dates' tab. I have set an UPPER command on Date's tab as the new program we are using is case sensitive with data whereas Excel isn't. Would it be possible to set a similar rule for the 'Leave Tracker' Tab so it will only accept UPPERCASE letters instead on lowercase.

I'd really appreciate your help on this again. :)

Thanks
Kanu
 

Attachments

  • Leave Tracker NEW!.xlsm
    90.4 KB · Views: 2
You must log in or register to reply here.
Back
Top