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
Image result for accountant picture

Crunchin’ da numbers

Recently I went in to do some training with a new manager who had been tearing his hair out with his Excel sheet.

The spreadsheet had already been set up for him and the previous incumbent had done some interesting array formula variations using If and Sum – and it worked fine actually. The only problem was that this new user had no idea how to amend or fix this formula. He need a-rray of sum(ifs)shine…sorry.

I decided to show him how to use the SUMIFS function to do exactly the same thing – only easier and no fancy CSE (Ctrl-Shift-Enter) moves as required to create an array formula….

The only data he really required was to show the spend to date for the year (and you can see that formula in the Spent Year to Date sheet. I could have set it up as a table but I wanted to make sure he understood what we had done here so 5000 rows allows lots of room for expansion.

Couple of things to note:

  1. All the criteria ranges have to be the same “height” i.e. 4:5000. Because alas, the SUMIFS won’t work otherwise…
  2. All the criteria ranges are fixed in this question (i.e. I used F4 to fix them) but the criteria1 is not because I needed that to change as I copied it down.
  3. You can check the answers by using the Filter option (click in list – Data – Filter)

Of course I got home and found myself idly wondering – well what if he needed the months as well. So I thought I’d experiment with that – as you do…

So I found I had to add another column to extract the month from the date which I added to the Month column

=TEXT(A4,”mmmm”)

Then in the Spent Month by Month sheet, I put the months across the top and entered the following formula:

=SUMIFS(‘Procurement list’!$D$4:$D$5000,’Procurement list’!$B$4:$B$5000,$A4,’Procurement list’!$E$4:$E$5000,’Spent month by month’!B$3)

A quick way to enter the formula is to:

  1. Highlight the entire sheet (B4:M24 in the Spent Month by Month sheet) 
  2. Enter the formula in the white cell (because I started highlighting at the top, this is where I had a white cell)
  3. Press Ctrl and Enter
  4. This will enter the formula in the entire highlighted area…

You can download the file here : Procurement_summary

Excel Products for Sale

Here are products from some of my favourite Excel authors and bloggers. Please note these are affiliate links so I do earn a small commission from every sale.

MrExcel.com

Mr. Excel (aka Bill Jelen) is one of the Excel gurus. I love his stuff. He also has a slightly bonkers delivery (What! Excel! Fun!)  that I thoroughly enjoy. And of course anyone who calls his Excel publishing book company Holy Macro! gets my vote.

MyExcelOnline.com

Xtreme Pivot Tables course

This is a very comprehensive course on pivot tables. It includes videos and workbooks. There are over 200 hours of videos on it and it takes you from having no knowledge of pivot tables to being a power user. If you know everything in this course, you know an awful lot about pivot tables. John explains the concepts in bite size chunks so they are easy to follow and even provides a tutorial spreadsheet so you can track your progress. This would be money well spent. If you would like to become the go-to person in your organisation for pivot tables, this would be an excellent starting point.

He also has a useful Pivot Table webinar…

This is also a useful chart helper. 

Debra Dalgleish – Contextures.com

Master 30 Excel functions in 30 days –This is a rather brilliant e-book which covers 30 functions including Vlookup,Match, offset. It includes an Excel file, e-book and links to videos that shows you how to do it – all for the princely sum of $10. What I particularly like about it are her descriptions, explanations and what the limitation of each function is. Highly recommended. If you want to upgrade your Excel skills this is a very worthwhile investment.

Excel UserForms for Data Entry

The Excel UserForms for Data Entry ebook kit will take you through the steps to build a UserForm that stores data on a hidden worksheet. No programming skills are required — everything is explained in simple steps, with written instructions, screen shots, videos and workbooks.

PivotPowerPremiumRibbon

The PivotPower Premium add-in saves you time and effort, when working with Excel pivot tables. For example, you can quickly:
-save and apply default pivot table settings
-clear old items from an Excel pivot table
-change all the data fields from the Count function to the Sum function

Chandoo.org

Click here to view more details about Excel Dashboard Video Tutorial
Click here to view more details on Excel Dashboard Templates
Click here to view more details on Excel Dashboard Templates Premium
Click here to view more details on Excel Formula Helper E-Book
Click here to view more details on Excel Formula Crash Course – View Option
Click here to view more details on Excel Formula Crash Course – Download Option
Click here to view more details on Excel School, Online VBA Classes & Dashboard Templates
Click here to view more details on Advanced Excel & Power Pivot Training Classes
Click here to view more details on Excel School & Online VBA Training Classes
Click here to view more details on Excel School & Dashboard Templates
Click here to view more details on Excel School – View & Download
Click here to view more details on Excel School – View Only
Click here to view more details on Excel Project & Portfolio Management
Click here to view more details on Advanced Power Pivot Class
Click here to view more details on Project Management Templates 2003
Click here to view more details on Project Management Templates 2007
Click here to view more details on Project Management Templates Both
Click here to view more details on Project Portfolio Templates
Click here to view more details on the VLOOKUP Combo Book
Click here to view more details on the VLOOKUP eBook
Click here to view more details on Online VBA Training Classes – View & Download
Click here to view more details on Online VBA Training Classes – View Only

 

 

 

 

Continue Reading

0

Got presented with an interesting dilemma recently. If you (or your children or any children you know) compete in anything there is usually an age cut off point. For example my son competes in music competitions and the cut off date for each year is 1st January i.e. if you are 17 on the 1st January of the current year, then you are competing in the 15-18 category. Even though at the time of the initial competitions (usually May) you may actually be 18.  So if you just had one child, that would be find but if you were running a class, that could be more problematic.

So this is what I did. Here is the file: age-calculation-with-cut-off

I set up a helper cell to show the cut off point. (that’s in cell D1)

I used the formula:

=DATE(YEAR(TODAY()),1,1)

Essentially, this will always show the current year: Year(today()), the Month number (1 in this case) and Day (1 in this case again). But if the cut off changed, you could change the day and month here. e.g. if the new date was 12th February, your revised formula would read as follows: =DATE(YEAR(TODAY()),2,12)

Then I used the lesser known DateDif function to calculate the difference between the date of birth  and the cut off point in years.  I put this formula in cell B3 and copied it down.

=DATEDIF(A3,$D$1,”y”)

A3 = date of birth

$D$1 = cell with cut off date

“y” = shows the answer in years.

I also added in a vlookup function which referenced the correct competition a child should be entered into. This looks up the child’s age. Then compares it to an age on the Competition Look-up sheet and returns the second column i.e. Competition Category. 

Excel Products for Sale

Here are products from some of my favourite Excel authors and bloggers. Please note these are affiliate links so I do earn a small commission from every sale.

MrExcel.com

Mr. Excel (aka Bill Jelen) is one of the Excel gurus. I love his stuff. He also has a slightly bonkers delivery (What! Excel! Fun!)  that I thoroughly enjoy. And of course anyone who calls his Excel publishing book company Holy Macro! gets my vote.

MyExcelOnline.com

Xtreme Pivot Tables course

This is a very comprehensive course on pivot tables. It includes videos and workbooks. There are over 200 hours of videos on it and it takes you from having no knowledge of pivot tables to being a power user. If you know everything in this course, you know an awful lot about pivot tables. John explains the concepts in bite size chunks so they are easy to follow and even provides a tutorial spreadsheet so you can track your progress. This would be money well spent. If you would like to become the go-to person in your organisation for pivot tables, this would be an excellent starting point.

He also has a useful Pivot Table webinar…

This is also a useful chart helper. 

Debra Dalgleish – Contextures.com

Master 30 Excel functions in 30 days –This is a rather brilliant e-book which covers 30 functions including Vlookup,Match, offset. It includes an Excel file, e-book and links to videos that shows you how to do it – all for the princely sum of $10. What I particularly like about it are her descriptions, explanations and what the limitation of each function is. Highly recommended. If you want to upgrade your Excel skills this is a very worthwhile investment.

Excel UserForms for Data Entry

The Excel UserForms for Data Entry ebook kit will take you through the steps to build a UserForm that stores data on a hidden worksheet. No programming skills are required — everything is explained in simple steps, with written instructions, screen shots, videos and workbooks.

PivotPowerPremiumRibbon

The PivotPower Premium add-in saves you time and effort, when working with Excel pivot tables. For example, you can quickly:
-save and apply default pivot table settings
-clear old items from an Excel pivot table
-change all the data fields from the Count function to the Sum function

Chandoo.org

Click here to view more details about Excel Dashboard Video Tutorial
Click here to view more details on Excel Dashboard Templates
Click here to view more details on Excel Dashboard Templates Premium
Click here to view more details on Excel Formula Helper E-Book
Click here to view more details on Excel Formula Crash Course – View Option
Click here to view more details on Excel Formula Crash Course – Download Option
Click here to view more details on Excel School, Online VBA Classes & Dashboard Templates
Click here to view more details on Advanced Excel & Power Pivot Training Classes
Click here to view more details on Excel School & Online VBA Training Classes
Click here to view more details on Excel School & Dashboard Templates
Click here to view more details on Excel School – View & Download
Click here to view more details on Excel School – View Only
Click here to view more details on Excel Project & Portfolio Management
Click here to view more details on Advanced Power Pivot Class
Click here to view more details on Project Management Templates 2003
Click here to view more details on Project Management Templates 2007
Click here to view more details on Project Management Templates Both
Click here to view more details on Project Portfolio Templates
Click here to view more details on the VLOOKUP Combo Book
Click here to view more details on the VLOOKUP eBook
Click here to view more details on Online VBA Training Classes – View & Download
Click here to view more details on Online VBA Training Classes – View Only

 

 

 

 

Continue Reading