+ Reply to Thread
Results 1 to 48 of 48

Staff Holiday Planner, where to start?

  1. #1
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Staff Holiday Planner, where to start?

    Hi all,

    I am not that au-fait with excel and I am trying to get to grips with getting a staff calendar together for our charity (max 30 employees). I want to be able to include the following:

    Employee names, department, annual holiday entitlement. Ideally, once the date range has been booked, should another in the same department request the same dates, then an alert would show as a reminder when allocating holidays. I just don't know how to find the simplest way of setting this kind of thing up.

    I hope that's not asking too much but I would appreciate your expert advice!

    Kind regards.

  2. #2
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Staff Holiday Planner, where to start?

    You should start by creating a table. A column for names, a column for dept, etc.

    The hard part will be determining how you are to represent the holiday time. You use the phrase "holiday entitlement". Lets lets start with this, when you say holidays do you mean any day they decide to take off or traditional holidays like Xmas, Easter, etc.? Then, does your phrase mean how many hours total they can take off, how many days total, or what specific holidays they can take off, etc.?

    Ideally, once the date range has been booked, should another in the same department request the same dates
    What this line really sounds like is a calendar.

    then an alert would show as a reminder when allocating holidays
    To whom would the alert display? The person requesting the time, the person approving the time?

    Who is actually using this file you intend to create?

    What are the rules for booking time?

    A sample file with some dummy data in it would help us help you. It possible data validation may work for what you want, or a macro, or something else. Its next to impossible to give you a complete answer when your OP has ~5 sentences.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    Look at this as a starter ...
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Thanks guys - sorry for not being clear enough, I mean it.

    So, I am the line manager of employees who would email me with a request for annual leave. This would be their normal annual entitlement which is a number of days from January 1st to December 31st. They would typically give me a start date and end date for their request and their total days.

    I wish to have a planner (similar to the leave tracker above) where I choose the employee and enter their dates requested. The employees details as shown on the planner would show their annual leave entitlement and what days are currently booked and what number of days are remaining from their initial annual total.

    Once the dates are entered, they would be populated into the calendar (an option to select the leave type - sick, bereavement etc. would be excellent) and their remaining days updated. I would like to have an alert as the person booking a holiday for someone if there is a clash with someone who had already chosen those dates which would give me the option to cancel or over-rule.

    I then would like to be able to have an overview per department (electrical, warehouse, logistics, contact centre, bric-a-brac, customer service, finance, marketing, volunteering) and see what the current leave booked looks like.

    Does that make more sense?

  5. #5
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    I have attached a sample sheet, which is essentially some of the values from the 'leave tracker' from JohnTopley yesterday. I don't have any formulae as I don't know how to do them. But if there was any way to converge the leave tracker in the previous post from John Topley and the additions I have no idea how to add, it would be brilliant.

    Many thanks!
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    We need Department added somewhere: in "List of Employees" ????

    And please update your profile with "Location" and Excel version(s) used

  7. #7
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Hi John,

    I had forgotten the department bit. I also included some additional staff members to have everyone and their department.

    Profile updated, thanks for reminding me

    Thanks.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    See the attached:

    I added "Departments" to "List of Employees" and then used "Look up" to record Department against "Employee Leave Tracker"

    I changed formulae for Conditional formatting ...

    =IF($C$2="Employees",COUNTIFS(lstEmpNames,valSelEmployee,lstSdates,"<="&C6,lstEDates,">="&C6,lstHTypes,'Leave Types'!$B$7)>0,COUNTIFS(lstDeptNames,valSelEmployee,lstSdates,"<="&C6,lstEDates,">="&C6,lstHTypes,'Leave Types'!$B$7
    )>0)

    The higlighted change for the "Leave Type"

    I added drop down in "Calendar View" to select "Employees" or "Department" which then produces the appropriate list in column L

    I added a couple of test cases of the "Employee Leave Tracker".
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Hi John,

    Thanks very much for that, it's amazing what you have done!

    I made a schoolboy error by forgetting to add the holiday entitlement for each person. I have added the original entitlement for each individual. When selecting employee and then the relevant employee, would it be possible to then show their original and remaining entitlement? One final element, is it possible to have an alert when duplicate holidays are requested from people in the same department? The 'employee leave tracker' would grow through the year? That way an alert would prompt me to check. As an idea (and this may be impossible), if I agree to two people being off at the same time in the same department, is there any way to show the duplicated dates on the calendar as flashing colours? This would just be to differentiate from single requests?.

    Many thanks.
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    Do you want the balance of entitlement shown in "Employee Leave Tracker" or "List of Employees": and is this simple "Vacation" time ?

    I suspect an alert will require VBA code. And I don't know about "Flashing" colours I(if even possible)!!!
    Last edited by JohnTopley; 01-10-2018 at 12:50 PM.

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    .... I need to calculation days leave so I assume a working week is Monday-Friday so any calculation (End vs Start dates) exclude weekends.

    And we need to consider "National holidays" which I recall you had in your "template" planner.

  12. #12
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    It would be perfect if it was visible on the main calendar view on selecting the employee, but I don't know how difficult that would be.

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    For an employee, it could easily be put on
    the main calendar.

    I need answers to my previous questions in posts #10/#11 plus a file with "national" holidays if possible.
    Last edited by JohnTopley; 01-10-2018 at 01:25 PM.

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    See attached:


    Named range "Holidays" in sheet "Tables"

    "List of Employees"

    in E4 Balance of leave based on "Vacation")

    =[@[Annual Leave Entitlement]]-SUMIFS(LeaveTracker[Leave Days],LeaveTracker[Employee Name],[@[Employee Names]],LeaveTracker[Type of Leave],"Vacation")


    in "Calendar View"

    in T2

    =IF($C$2="Employees","Vacation Balance","")

    in Y2

    =IF($C$2="Employees",VLOOKUP(valSelEmployee,'List of Employees'!$B$4:$E$27,4,0),"")

    Details only appear if "Employees" selected in C2

    "Alert" added in column H of "Employee Leave Tracker"

    in H4

    =IF(SUMPRODUCT(($C4<[End Date])*($D4>=[Start Date]))>1,"!! Dates Overlap !!","")
    Attached Files Attached Files
    Last edited by JohnTopley; 01-10-2018 at 02:28 PM.

  15. #15
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Hi,

    I think the additions are great. I have added a column under 'List of tables' to answer #11 which shows that the majority of staff work Monday to Friday and there are a couple of exceptions where people work weekends. Regarding #10, the balance of entitlement is simply from their holiday time. I like the alert, it's a perfect work around and gets me to see where the clashes are. The national holidays are the bank holidays that you have already included. My assumption is that I would adjust those each year to reflect the changes.

    Thanks.
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    Re working week:

    this requires changes to this formula in "Employee Leave Tracker" column G

    =IF(B4="","",NETWORKDAYS.INTL([@[Start Date]],[@[End Date]],1,Holidays))

    The highlighted value is to set the weekend to Saturday/Sunday: an alternative is a text string "0000011" [See help on NETWORKDAYS>INTL]

    =IF(B4="","",NETWORKDAYS.INTL([@[Start Date]],[@[End Date]],"0000011",Holidays))

    So I leave you a little challenge:

    Given the "Working Week" in "List of Employees" we need to have a variable value (parameter) to replace the 1 or "0000011". and do a "look up" based on the Employee to retrieve the parameter.

    Have a go!

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    Not sure if you have tried to work out a solution so here is one !

    Added column G in "List of Employees" with parameter showing weekend (of form "0000011")

    In column G of "Employee Leave Tracker"


    =IF(B4="","",NETWORKDAYS.INTL([@[Start Date]],[@[End Date]],VLOOKUP([@[Employee Name]],Employees,6,0),Holidays))
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    For some reason, I thought there was a response to this that I saw on my mobile phone last night, but I am unable to see it anymore.

    Have a great weekend.

  19. #19
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Bizarrely, I can now see it!

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    Are we finished ? If so ..

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  21. #21
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    If it's ok, I will load in some data, and test to see if there were any questions regarding functionality over the weekend and either ask another question, or solve it

    Thanks so very much

  22. #22
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Hi,

    On the calendar view page, there is data below the calendar that shows colour coded boxes. Could that be set to report on the current year (chosen by the selected year in question)? This way, when I choose an employee, I can see what they have taken at a glance (and so can they). It will be more beneficial for the year showing than the previous year.

    One extra question that may or may not be possible, but I want to explore the option:

    Two members of staff work alternate weekends on a two week rota basis. The way we work it is that we choose a two week format. Week 1, the first person works Monday to Thursday, Friday off and then works Saturday and Sunday. Week 2, starts with Monday off and then works Tuesday to Friday with Saturday and Sunday off. The second person starts their first week with the pattern of the first persons week 1 so that they alternate. Does that make sense? I have attached a table for you. And I have also updated names etc. on the original sheet so that the extra table makes sense.

    Many thanks!
    Attached Files Attached Files

  23. #23
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    For Question 1

    change ALL formulae:

    =SUMIFS(LeaveTracker[Leave Days],LeaveTracker[Employee Name],valSelEmployee,LeaveTracker[Start Date],">="&DATE(Calendar_Year,1,1),LeaveTracker[End Date],"<"&DATE(Calendar_Year+1,1,1),LeaveTracker[Type of Leave],'Leave Types'!B4)

  24. #24
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Really sorry but where on which tab would I need to change the formulae?

    Many thanks.

  25. #25
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    "Calendar View" N20 etc

  26. #26
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    I assume the Rota applies to calculation of Leave ?: as a minimum we need start date of Week 1.

    This starting to get rather complex.

  27. #27
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Hi,

    Yeah, this is principally down to leave, And the only reason I asked is because everything else works so fantastically, and this an unusual anomaly but one that I at least wanted to ask the question about. Week 1 starts from today. If it's not possible, then I completely understand.

    Thanks.

  28. #28
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    The option I am looking is using Excel WEEKNUM function so I have a 52/53 week rota for the two Employees.

    I would also require a "fixed" table for each employee: this will be the current column G in "List of Employees"

    Given I know the Week Number I can assign the W/E parameter to each Employee.



    I added a field in "List Of Employees" to determine whether week was Fixed or Variable (your Rotating employees).

    I have a table in "Tables" columns F & G which defines the rota.

    I have not yet added logic to the "W/E Parameter". it could get tricky if a holiday went over a rota boundary i.e part in Week 1 and part in Week 2

    See attached as a possible template (some data is repeated until final solution determined)
    Attached Files Attached Files

  29. #29
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Hi,

    It looks great, really great - thanks for ensuring that the totals tally in the calendar view (n20 etc.). When you mentioned that you had not yet added logic to the 'W/E Parameter', do you mean there are more formulae to add in there or will there be an element of common sense to be applied when looking at the holidays. I completely understand that with anything there needs to be an element of cross checking to ensure all works well. I am going to fill it with accurate rather than test data to give it a full road test. If that then works as I believe it will, then I will mark it as solved . I wish I had the level of knowledge that you have, but I understand that it takes years to gather!

    Thanks, as always.

  30. #30
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    There needs to be (an) additional formula to check the "Weekend" logic which is used in the vacation calculation to cater for your two "rotating" employees.

    I plan to use WEEKNUM function to determine which shift rota they are on. As I pointed out earlier, if a holiday is spread over two (or more) "shift weeks" then it get a bit complicated I have not thought too deeply about how to do this!).

    You can still road test it using the current formula but without the ability to rotate the "weekend parameter" for Sion/Miranda. Just set these to one of the two shift patterns.

  31. #31
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    See the attached: "Tables" now has a weekly "rota" for all employees which is used to calculate "Leave".

    For the staff on the 2-week rota, holidays will need to be added as weekly periods to ensure the correct allocation of days.

    in G4 of "Employees Leave Tracker"

    =IFERROR(IF(B4="","",NETWORKDAYS.INTL([@[Start Date]],[@[End Date]],INDEX(Rota,MATCH([@[Employee Name]],Tables!$F$1:$AB$1,0),MATCH(WEEKNUM([@[Start Date]]),Tables!$E$3:$E$55)),Holidays)),"")


    "Rota" is named range for employees week rota parameters in "Tables"
    Attached Files Attached Files

  32. #32
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Hi,

    I have been loading in live data, and the only two people I can't get to work are 'Craig Bowles' and 'Jacky Ward' as they don't calculate days. I can't seem to fathom how to get them to either.

    Thanks very much

  33. #33
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Hi,

    I have added the live data and attached the form. You will be able to see from the employee leave tracker tab that certain cells are not calculating. I wish I could fathom out where to look.

    Thanks in advance.
    Attached Files Attached Files

  34. #34
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    My error:

    formula in G4

    =IFERROR(IF(B4="","",NETWORKDAYS.INTL([@[Start Date]],[@[End Date]],INDEX(Rota,MATCH(WEEKNUM([@[Start Date]]),Week_Rota,0),MATCH([@[Employee Name]],Tables!$F$1:$AB$1,0)),Holidays)),"")


    Plus some the "W/E Paramters" were not text so NETWORKDATS.INTL failed.
    Attached Files Attached Files
    Last edited by JohnTopley; 01-19-2018 at 06:47 AM.

  35. #35
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Hi, one question.

    On Miranda Keen, a holiday range of 23rd May to 28th May (I believe week 21 into week 22) has been requested. As this person splits over a two week rota, I put the first week (23rd to 27th) and it correctly calculates 3 days. I then added 28th separately (as shown in the attached) and it doesn't count the day. I may be doing something incorrectly, but just wanted to check.

    Cheers.
    Attached Files Attached Files

  36. #36
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    First ,week 21 is 20 to 26 May: hence week 2 is 27 May to 2 June

    So first holiday is 23 May-26 May (3 days)

    second is 27 May-28th May

    27th may is a Sunday (non-work day for Miranda) and 28th May is a bank holiday so 0 vacation.

    See attached ans see columns AC:AE in "Tables" (as a check)
    Attached Files Attached Files

  37. #37
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Well, that's perfect And I knew it would have had to be something simple! Thanks very much. Final few checks with management to review and then I will mark as solved!

  38. #38
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Hi,

    A few questions:

    1. On the 'calendar view' what exactly is the box below 'days on leave' referring to? (not essential, just attempting to second-guess management questions).
    2. When a person has already booked holidays out of this years entitlement and I change the year, the balance of the holidays that year does not reset to the beginning but keeps the old value.
    3. At present, on the 'calendar view', the boxes keep a colour coded total of all the various leave types. So far, they do not keep a tally of the previous year numbers if I change the year to 2019 for example.

    Thanks in advance

  39. #39
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    I am attempting to add more people for leave and as you will see from the attached, the calculation is not showing the 'leave days' when booking in time for people. I wonder if you could help.

    Many thanks.
    Attached Files Attached Files

  40. #40
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    I don't see any problem other than last entry in "Employee Leave Tracker"("Marc Powell") had blank dates.

    It won't show on the "Calendar View" as year 2019!!!

    I suggest you make C3 on "Calendar View" a data validation entry (with years 2018,2019,2020 etc)
    Last edited by JohnTopley; 01-23-2018 at 08:45 AM.

  41. #41
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Sorry about that, I was just showing one of the managers how good it was and I forgot to change it back to 2018 before I had saved it. The last employee Marc Powell (or any other person) didn't bring up the number of leave days when entering the dates requested as the all the others had automatically calculated the remaining leave days and taken them off the remaining holiday entitlement (as shown in Calendar View). Did the questions in #38 make sense?

    Thanks once again.

  42. #42
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    The wrong formula was in the last few cells: copy down formula from G4.

    Re post #38:

    1Total leave for selected Employee: formula incorrect

    =SUMIFS(LeaveTracker[Leave Days],LeaveTracker[Employee Name],valSelEmployee,LeaveTracker[Start Date],">="&DATE(Calendar_Year,1,1),LeaveTracker[End Date],"<"&DATE(Calendar_Year+1,1,1))

    2. Current calculation assumes all hoidays entered are for current year so no check is made on the actual year. Entitlement assumes current year.

    3. Previous years holidays if recorded in "Employee Leave Tracker"


    If you require to have leave entitlement for a number of years then we need to add a table of leave entitlement per employee for each year.
    Last edited by JohnTopley; 01-24-2018 at 07:05 AM.

  43. #43
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Hi,

    I was just curious about a question asked by one of our senior management team. What about when someone books a half day? Without taking too much time, what is the feasibility of having the option to book half a day annual leave? I realise that it may be too complex, but i wanted to ask the question.

    Thanks very much.

  44. #44
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    One way is override the formula in "Employee Leave Tracker" with a value 0.5.

    If someone had 3 full days and one half day consecutively then the override value would be 3.5

    the alternative is to have a separate column for half days which would contain 0.5 so for a consecutive 3.5 day period you would enter Start/End days to give 4 days holiday and then SUBTRACT the 0.5

  45. #45
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    I wonder if you would be able to locate the error in one of the simpler calculations for me. In particular, the last entry in the Employee Leave Tracker. It shows Johnny Horton having the 4th to the 17th May off - which is ten working days - but calculates as 9. I can't see where the error lies.

    Many thanks.

    ***Please ignore, didn't have my thinking hat on!***
    Attached Files Attached Files
    Last edited by Sionos; 02-03-2018 at 05:38 AM.

  46. #46
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,995

    Re: Staff Holiday Planner, where to start?

    7 May is a Bank Holiday perhaps ?: second time you have missed holidays!!!

  47. #47
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Staff Holiday Planner, where to start?

    Which do you think is the best solution to allow employees to simply access their name to see what days they have left or have taken, or even what their department has booked without being able to do anything other than use those two drop down boxes? Everything else is perfect That way, no formulae can be tampered with.

    Many thanks.

  48. #48
    Registered User
    Join Date
    08-25-2021
    Location
    Berlin,Germany
    MS-Off Ver
    2016
    Posts
    16

    Re: Staff Holiday Planner, where to start?

    Hello @Sionos,

    thank you for the leave tracker.
    I downloaded the final version.
    How to use table based on employee names? So I would like to select the employee's name from the drop-down list, then his absences should be displayed in the calendar.
    Thank in advance.

    Regards from Germany /Berlin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Staff Holiday Planner
    By ABSTRAKTUS in forum Excel General
    Replies: 6
    Last Post: 12-07-2017, 11:22 AM
  2. Staff holiday planner
    By Partridge in forum Excel General
    Replies: 6
    Last Post: 08-04-2017, 09:05 AM
  3. Replies: 5
    Last Post: 02-14-2017, 04:27 PM
  4. working on a holiday planner based on various holiday anniversary dates
    By marktc19 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2017, 09:26 AM
  5. Staff holiday planner
    By chloe_cub in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-16-2016, 07:23 AM
  6. Staff Planner - How To Set Up UserForm to Generate Data into Staff & Date Spreadsheet
    By Marie Snell in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-19-2013, 11:04 PM
  7. Holiday Planner show holiday taken?
    By Mac5 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-23-2006, 01:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1