0

A common issue for many organisations is tracking time-in-lieu i.e. someone has a certain number of days/hours holidays(vacations) allocated but they can increase that by doing overtime and then taking time off – “in-lieu”.  While some organisations have a system in place, a lot don’t.

This tutorial will show you the following:

  • How to set up a sheet for each employee that automatically tracks and summarizes their time in lieu (all they have to do is enter the date, number of hours overtime and time taken off).
  • This sheet will also calculate a balance so they can see how many hours holiday time they still have outstanding.
  • How to set up a sheet that summarizes all those hours for the manager so that he/she can get the big picture at any time.

The tutorial below takes you through it step by step. If you want to view the file straightaway you can download the file with dummy data (so you can see how it works) or without dummy data when you are ready to experiment with your own data.

Download completed file with dummy data

Download completed file without dummy data (but with formulas)

Scenario

  • Wylie Coyote is a manager. She has 5 staff .
  • Each member of staff has an allocation of total holiday hours (cell I1 in the file below)
  • Each day is taken to be 8 hours
  • Extra hours can be done per day – they are recorded as Overtime. Every time an employee does overtime, the date will be entered in the Date column (Column B), and the number of hours entered into the Overtime column (Column C)
  • When the employee takes time off – this is entered in the Time in lieu/holidays column (Column D).
  • The balance is calculated automatically – based on the total number of hours for holidays, plus overtime minus Time in lieu/holidays.
  • The month of that date is entered automatically in Column A – this will be used later for summarizing hours. This is done via a formula

Step 1 – Set up the Employee sheet

View the video here to see how this is done. Some sample data has been entered. Thanks to RGonzo1971 of Experts’ Exchange for help with the dynamic formula.

You can download the file at this stage.

Step 2 – Summarizing the data month by month.

So right now our sheet shows the employee how many hours they have outstanding at any point in time (assuming they have entered all their hours up to date)

However, Wiley Coyote wants to see two things – she wants to see a monthly summary for each employee on their sheet and she also wants to have a picture overall of how much overtime and time in lieu has been taken.

So in the same sheet we set up the formulas to summarize on a monthly basis how much overtime and time-in-lieu each person has taken.

See video below to see how this is done. I have used the Sumifs() function and you can view the tutorial on it here.

  1. Fill in the months of the year from K6 to K17 down
  2. In cell L5, we will use a SUMIFS() function to calculate the amount of overtime per month
  3. In cell M5, we will use a SUMIFS() function to calculate the amount of holidays/time-in-lieu each person has taken.

You can download the file with the summary completed at this stage.

Step 3 – Creating one for all our employees

We now have our first sheet set up – next thing we have to do is make a copy of this for every other employee.

See a video here that shows you how to make a copy of the sheet and rename them accordingly.

You can now download a file where it has been set up for 5 employees.

Step 4 – Creating a bird’s eye view sheet 

The last step is to create a sheet that allows the manager to see how much overtime is taken by each employee by month. This is to help her to do the following:

Make sure that employees are taking their time-in-lieu within the month as far as possible and to make sure holidays are taken within the year.

The summary sheet will summarize by employee and month what their current balance is.

View both completed files here. Again, if you have any questions, just get back to me.

Download completed file with dummy data

Download completed file without dummy data (but with formulas)

 

Continue Reading

0

This is my first blog post of 2017. I have to say I do love the energy of a new year. My favourite time of the year are those days between the “two Christmases” i.e. 25th December and 6th January. There is no pressure. Just movies to be watched, sweets to be eaten, walks to be taken and generally relaxing all around. So I was looking back over my posts and I realised that I had no blog post specifically for filtering – which is probably one of the features you use most in Excel.

In a previous post I have covered how to set up a list and I’ve devoted a big chunk of Chapter Two of my book (Your Excel Survival Kit)  to it.

But here I am just going to focus specifically on the mechanics of filtering. So what is filtering? If you have a list in Excel the chances are you are not going to spend your day reading through it from the beginning to the end. If you are, could I suggest that maybe you have just a little too much time on your hands…. No, usually you will need to be able to find specific pieces of information out of it. The best way to do that is via filters. So how do they work?

I am using a file called products_filter which you can download to practise on.

(You can download the file, by doing a right click on it, and then click on Save As, and then choose where you want it saved to)

Let’s say I want to answer 5 questions from this data set.

  1. How many of the products are Black?
  2. How many have a List Price over 1000
  3. How many have a Color of Blue and have Sport in the Product Name
  4. How many started (ScdStartDate) after 1998 and have a ListPrice between 50 and 100
  5. How many Product Models have Mountain in the name, have a color of Black or Silver (NOT Black/Silver) and have a list price of over 1000.

In this video I show you how to do these filters, clear down the list after each one and also how you know if there is a filter applied.

Video 1 – How many of the products are Black?

Video 2 – How many have a List Price over 1000

Video 3 – How many have a Color of Blue and have Sport in the ProductName

Video 4 – How many started (ScdStartDate) after 1998 and have a ListPrice between 50 and 100

Video 5 – How many Product Models have Mountain in the name, have a color of Black or Silver (NOT Black/Silver) and have a list price of over 1000.

Homework

(Use the same file)

  1. Identify how many of the products are blue? (28 – check bottom left hand corner)
  2. Identify how many products have a list price of less than 500 (hint: check number filters for Less than) (202 – again check bottom left hand corner of screen)
  3. Identify how many products have Road in the ProductName and have a Cost of less than 500 (22 – again check bottom left hand corner)
  4. Identify how many started in 2007 and 2006 (tick both boxes) and have a Cost between 250 and 500 (50 – again check bottom left hand corner)
  5. Identify how many products with a product code that starts with FW and a SCDStartDate in 2006 and a list price greater than or equal to 100 (5 – again check answer in bottom right hand corner)

Continue Reading

0

Debra Dalgleish (http://www.contextures.com) is one of my Excel heroines and the pivot table guru. She is someone whose work I regularly reference in class.

I recently asked her to help me answer a question that regularly comes up in class when people ask about pivot tables – how do I know what to put where?  She answers the question comprehensively in this blog entry.

http://blog.contextures.com/archives/2016/09/15/how-to-plan-a-pivot-table/ 

However three tiny additions I would put with this is the following:

  1. Generally put whatever it is you want to do the calculations on in the Value area
  2. I tend to put dates in the Row Labels section, group them and then move them around.
  3. Get started, put in something – you can always move and re-organise it if you don’t like it…

Enjoy!

Continue Reading