Generate list of holiday from tracker workbook

**~TaC~** · 12-21-2019, 05:26 AM

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

**~TaC~** · 12-22-2019, 05:36 AM

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

**Olly** · 12-22-2019, 07:29 AM

You can use Power Query:

Please Login or Register  to view this content.

**Olly** · 12-22-2019, 11:51 AM

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.

**~TaC~** · 01-08-2020, 09:08 AM

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

**Olly** · 01-08-2020, 09:35 AM

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

Please Login or Register  to view this content.

**~TaC~** · 01-08-2020, 10:09 AM

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?

**Olly** · 01-08-2020, 10:42 AM

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

**Olly** · 01-08-2020, 10:49 AM

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.

**~TaC~** · 01-08-2020, 11:21 AM

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?

**Olly** · 01-08-2020, 11:32 AM

Does my post #9 fix this?

**~TaC~** · 01-08-2020, 11:48 AM

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?

**Olly** · 01-08-2020, 11:56 AM

Sounds like you deleted too many rows....

Here you are, the full edited query:

Please Login or Register  to view this content.

**~TaC~** · 01-08-2020, 12:14 PM

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

Generate list of holiday from tracker workbook

LinkBack

Thread Tools

Rate This Thread

Display

Generate list of holiday from tracker workbook

Re: Generate list of holiday from tracker workbook

Re: Generate list of holiday from tracker workbook

Re: Generate list of holiday from tracker workbook

Re: Generate list of holiday from tracker workbook

Re: Generate list of holiday from tracker workbook

Re: Generate list of holiday from tracker workbook

Re: Generate list of holiday from tracker workbook

Re: Generate list of holiday from tracker workbook

Re: Generate list of holiday from tracker workbook

Re: Generate list of holiday from tracker workbook

Re: Generate list of holiday from tracker workbook

Re: Generate list of holiday from tracker workbook

Re: Generate list of holiday from tracker workbook

Thread Information

Users Browsing this Thread

Similar Threads

Holiday Tracker

[SOLVED] Multiple Entry Locations To List For Holiday Tracker

[SOLVED] Holiday Tracker

Holiday planner/tracker

Holiday Leave Tracker Help

Holiday Tracker

holiday tracker

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions