+ Reply to Thread
Results 1 to 14 of 14

Generate list of holiday from tracker workbook

  1. #1
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    160

    Generate list of holiday from tracker workbook

    Hi,

    Most things I have a go at and some things I can work out but, I have an idea of something I want and don't have a clue where to start this time.

    I have attached a holiday tracker for work which when I input the amount of hours holiday worked each day as can be seen on the days of the year, it gives me a total use, remaining, percentage... ect.

    I have removed the names for protection reasons so you can not see a lot of the data but that shouldn't matter for this example.
    Lets use the Payroll Number(E) instead of Name(D) then I can change it later.

    What I would like to do is generate a list of holiday booked to make it easier to see/review, and is it possible to make this live so that as holiday is added/removed the list updates?

    Initially I was thinking a separate workbook, but a new sheet would work just as well, no fantasy formatting just a list something like..

    Week 48
    519916 01/01/20
    520643 01/01/20
    526066 01/01/20-03/01/20
    525183 01/01/20
    514962 01/01/20-02/01/20
    519229 01/01/20
    529411 01/01/20
    596452 01/01/20
    621273 02/01/20

    So that would be the result for week 48, but a continuous list for the entire year.

    Not sure if this is possible, like I said its just an idea.

    Thanks in advance
    Tez
    Attached Files Attached Files
    Last edited by ~TaC~; 12-22-2019 at 05:34 AM.

  2. #2
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    160

    Re: Generate list of holiday from tracker workbook

    Thinking about it, it doesn't need to live update as I could put in a macro button or something to update as and when data is changed.

    Just another thought

  3. #3
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Generate list of holiday from tracker workbook

    You can use Power Query:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Generate list of holiday from tracker workbook

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

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  5. #5
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    160

    Re: Generate list of holiday from tracker workbook

    Hi all,

    Sorry but I've had resort to marking this as unsolved.

    Thanks again to Olly for the work in Post #3 which is exactly what I requested and it still seems to give the results I asked for (in one respect).

    As I previously stated I have never seen or heard of Power Query so I thought I'd have a play around with it and see if I could work things out myself...

    I can't lol

    So my next question is, looking at the sample in OP and file in Post #3, If, for data protection reasons, you copied down E6: to D6: using payroll number as name. How would I remove Payroll Number column from table and include Name instead, if that makes sense?

    I originally used column E so names weren't on display thinking it would be easy enough to change, how wrong I was.

    Thanks
    Tez

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Generate list of holiday from tracker workbook

    If you want to group by Name instead of Payroll number, make these changes (in red):

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    160

    Re: Generate list of holiday from tracker workbook

    Thanks, works fine. I was using the editor and had worked out that I had to change "Removed Other Columns" but that was as far as I had got.

    Now that I am filtering by name I can see easier what is/isn't working. The "Holiday Dates" is not including weekends and therefore the "Holiday Hours" is not calculating correctly?

  8. #8
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Generate list of holiday from tracker workbook

    Don't understand. Can you give me an example of where it's wrong, and what it should be?

  9. #9
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Generate list of holiday from tracker workbook

    Ah, I see what you mean.

    Okay, remove these two lines:
    Please Login or Register  to view this content.
    Then change the following line:
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    160

    Re: Generate list of holiday from tracker workbook

    Files attached are "sample", as OP but with Names not Payroll "sample1" is the new version of your file with Names not Payroll.

    In "sample", if you look at "Employee Two" and scroll along to Week 18, June 6th & 7th, this employee is on holiday for 2 days using 15 hours.
    In "sample1" filter by name for "Employee Two" and these dates/hours are not displayed.

    Only Monday-Friday dates/hours are being calculated, this is the same for all other employees.

    On "sample1" you will see row 6 shows 31/07/2020 7.5 hours whereas "sample" shows holiday for 31/07/2020 - 02/08/2020

    All holiday needs to be calculated so that the dates are displayed in your file, if you get what I mean?
    Attached Files Attached Files

  11. #11
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Generate list of holiday from tracker workbook

    Does my post #9 fix this?

  12. #12
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    160

    Re: Generate list of holiday from tracker workbook

    Sorry didn't see that reply...
    Made changes and this error appears

    Expression.Error: The import Expanded MyTable matches no exports. Did you miss a module reference?

  13. #13
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Generate list of holiday from tracker workbook

    Sounds like you deleted too many rows....

    Here you are, the full edited query:

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    160

    Re: Generate list of holiday from tracker workbook

    That seems to work perfectly, right columns and data just as I needed.

    Thank you so much for your time and patients, I don't think I'll get my head around Power Query though haha.

    Again thanks
    Tez

+ 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. Holiday Tracker
    By Wullie1974 in forum Excel General
    Replies: 3
    Last Post: 01-24-2019, 05:20 PM
  2. [SOLVED] Multiple Entry Locations To List For Holiday Tracker
    By FoxDangerMolder in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-05-2016, 05:40 PM
  3. [SOLVED] Holiday Tracker
    By Kris_2999 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-26-2014, 03:34 PM
  4. Holiday planner/tracker
    By ukphoenix in forum Excel General
    Replies: 6
    Last Post: 04-16-2012, 07:37 AM
  5. Holiday Leave Tracker Help
    By subtilty in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-30-2011, 10:39 AM
  6. Holiday Tracker
    By Benjamin2008 in forum Excel General
    Replies: 4
    Last Post: 02-06-2010, 08:38 AM
  7. holiday tracker
    By vikfowler in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-18-2008, 06:27 AM

Tags for this Thread

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