Posts Tagged ‘xls’

2016 and 2017 staff holiday Excel planner and one page Excel calendar

Sunday, August 30th, 2015

The requests for the 2016 excel staff holiday planning spreadsheet have been coming in so I have made it as well 2017 one. Also I’ve uploaded the 2016/2017 one page excel calendars.

Staff holiday planner

First up here are my free spreadsheet that can help you track staff leave/holiday, training, sick days, offsite days, etc.

2016 2017 staff holiday planner

They come set up with formulas that can automatically track the total number of holiday days remaining (columns C&D) for each employee. If you don’t want to use this simply delete those columns.
The default formula subtracts 1 or 0.5 from the remaining holiday if the employee cells contain the words ‘holiday’ or ‘half. You can copy and paste (ctrl-c, ctrl-v) the coloured tags (holiday, half, training, etc) from the top left into the staff planning cells and the totals are automatically updated.

Note that the formulas measure the tags from the very start column (which is the year before) to the very end column (which is the year after).

See the 2012 planner for some customisation examples.

The week numbers are based on ISO 8601, and the top and left parts of the staff planner are frozen so you can always see them – this makes navigation easy. Here is the download link:

Download 2016 staff holiday planner spreadsheet – 22kb
Download 2017 staff holiday planner spreadsheet – 22kb

As the staff holiday planner has over 365 columns you won’t see the whole year if you open it with an old spreadsheet package such as Excel 97. You’ll need to use a more modern spreadsheet package such as Excel 2007 or LibreOffice (which is free). One workaround if you are forced to use Excel 97 at work is for you to use LibreOffice at home to split the spreadsheet into 2, one part for H1, and one for H2.

One page Excel calendar

I’ve put the whole year in this one page Excel spreadsheet.
2016 2017 one page excel calendar

You should be able to load the XLS using any version of Microsoft Excel from 97 onwards, or using the free OpenOffice or LibreOffice.

2016 one page Excel calendar – 13kb
2017 one page Excel calendar – 13kb

The one page Excel spreadsheet calendars and the staff holiday planners are zipped up. If your OS won’t automatically open the zip files you can download the free 7-Zip to unzip them for you.

Monthly Excel planner

Monday, December 19th, 2011

I’ve previously produced a weekly planner Excel spreadsheet, but recently I’ve needed to use a monthly planner with hourly time slots.

It is a simple one page monthly planner. The times from 6am-1am are along the top, and the days of the month from 1-31 are down the side. For the months with 28, 29, or 30 days long you can just cross out or delete the extra days. If you need a different time range it is very easy to edit as well.

monthly excel planner

You can either use this on screen, or print it out. With Microsoft Excel there is an easy option to specify that you want the planner to fit on a single A4 sheet of paper. In the ‘Page Setup’ window set the orientation to Landscape and tell it to fit to 1 page wide by 1 page tall. Here is the free download link:

Download monthly Excel planner – 9kB zipped

I’ve saved this as a Microsoft Excel 97 XLS file so it should work in all old or new version of Microsoft Excel or OpenOffice.

Three point estimation spreadsheet

Saturday, February 12th, 2011

If you are trying to make a three point estimation spreadsheet for Excel but are struggling with the formulas, then this free Excel download that I’ve made may help you.

three point estimation person spreadsheet

It has fields set up for entering data such as the task name, person responsible, and then the best, worst, and most likely estimates. You can also enter a contingency value as a letter. One of the spreadsheet tabs lets you set what percentage of contingency applies for each letter.

three point estimation contingency

If you like your estimates to be based on the most likely plus the contingency, you’ll see this automatically calculated for you in the first yellow columns (you may have to copy the formulas down as far as you need them).

If you prefer your estimate to be based on E/SD/Confidence values you’ll find them to the right of the sheet. Also available are the average and standard deviation (of the best/worst/most likely). The second yellow column is the 98% confidence plus contingency value.

three point estimation confidence values

Finally if you want to see a quick summary of how much work is allocated to each person there is a tab which gives you this information.

three point estimation person view

This spreadsheet is by no means ready for immediate use, you’ll probably want to customise it, remove the columns that you don’t need, and verify that the formulas do what you want and are correct. But at least it may help you produce the three point estimation spreadsheet you want more quickly.

Download the three point estimation spreadsheet – Excel XLS (16kb zipped)

It has been tested on Microsoft Excel 2007 and is a free 16kb download.

2012 One Page Excel Calendar

Wednesday, December 22nd, 2010

At the time I write this 2010 is nearly over, which means that 2012 is just over a year away. If you are planning far in advance then you might appreciate this free downloadable 2012 one page Excel calendar. If 2012 is too far ahead then I have a 2011 one page excel calendar available for download as well.

2012 excel calendar

Just like in previous years this calendar features the whole year on a single page. It should fit on your computer screen, but you might need to adjust the zoom depending on your default settings.

There are two versions, one is plain, and the other has the UK bank holidays marked on it.

2012 one page Excel calendar – blank
2012 one page Excel calendar – with UK bank holidays marked

Both version of the calendar are free to download, and should work on Excel 97 and above, or on OpenOffice (which you can download for free).

2011 monthly calendar for Excel

Friday, December 17th, 2010

Here is a free monthly calendar for 2011. You can download it as a XLS spreadsheet for Excel, Open Office, or whatever spreadsheet application you use.

I’ve previously produced a one page calendar for 2011, but I had a request to produce a month by month version, with each month on a separate tab. Here is what September 2011 looks like.

2011 monthly calendar

And here is a zoomed in picture of the calendar months listed on the tab at the bottom.

2011 monthly calendar zoomed

It is a free download, and only 13kb zipped up, or 96kb uncompressed.

The days of the week are along the top in a frozen pane, and the weeks are along the side. I’ve put the week numbers in as well (ISO 8601 week numbers if these things matter to you).

Download the 2011 monthly calendar – Excel XLS (13kb zipped)

This calendar should be viewable on Excel 97 and above.

Present and gift logging spreadsheet

Monday, March 15th, 2010

Sometime it can be hard to think of what presents to give for birthday, Christmas, Valentines or anniversaries. Having a record of what presents you have given before can help to provide inspiration, and can help you avoid giving the same present twice.

present or gift logging spreadsheet

I’ve made a simple spreadsheet for logging gifts and presents. I’ve put some example data in there so you can see how to use it.

The rows can easily be filtered by the person’s name, or the event, by using the auto filter options in the header row. I’ve added some example auto-colouring which colours certain events (birthday, Christmas, and Valentines) to make them stand out.

The gift logging spreadsheet can be downloaded with this link – it is only 5kb zipped up.

Managing holiday money with an Excel burn down chart

Tuesday, March 2nd, 2010

If you go on a short holiday then it should be pretty easy to manage your holiday money. It isn’t too hard to track how quickly your foreign currency is running out when you are only abroad for a week or two.

If you are going on holiday for a number of months then it becomes a bit harder. When I recently went on holiday for three months I thought being able to record my available currency in the form of a burn down chart would be a useful thing to do.

I ended up creating an excel spreadsheet which when filled in gave me three lines on a graph.

holiday money burndown chart

  1. The first straight line (pink) goes down from the total amount of foreign currency on the first day of my holiday to 0 on the last day of my currency. This line shows where my currency reserves should be on average each day in order to spend all the money I took with me.
  2. The second line (blue) is the actual amount of foreign currency that I have left. A few times a week I would count up how much money I had left, and enter the data into the Excel spreadsheet. This would then update the second line. If this line is above the straight line then I’m under spending (on average), if the line is above the straight line then I’m over spending. The concept of under or over spending is of course ‘on average’. There may be good reasons why your spending during your holiday might be unevenly spread.
  3. The third line (yellow) is plotted against the axis on the right of the chart. This shows how much currency I have left per day on average.

Using the holiday currency burn down spread sheet

To make it easier to see how to use the spread sheet I have put a load of example data in the yellow cells. When you understand it you will have to remove the example data and enter your real data. Don’t delete anything in the red cells as these cells contain the formulas which will make it all work.

holiday money burn down spreadsheet

I have put comments in cells A2, B2, H2, I2 and I3 explaining what you need to put into these cells to get the spread sheet setup for your own holiday.

Then all you need to do is update it every few days and you’ll be able to see how fast your holiday money is burning down.

Download the holiday money spreadsheet from here (12kb).

Telephone call log spreadsheet

Monday, July 6th, 2009

It can be well worth keeping a log of phone calls to companies such as banks, utility companies and anyone else who you have an account with. You never know when it could be useful to have this information.

For example this week I received some unsolicited credit card cheques from Natwest. I have previously been sent these by Natwest and I remember that I phoned them up to ask them not to send them to me anymore. I know I have called them at least once and I suspect that I have called them twice. As they are clearly not honouring my preferences I am writing a letter of complaint to them.

Unfortunately I don’t know the dates (or even whether I made one or two) of my calls to them. I can still write the letter, but if I had recorded my calls to them I could have been very specific about details of when I told them not to send me any more of their credit card cheques.

Here is the call logging XLS spreadsheet that I’ve put together to record future calls.

Telephone call log spreadsheet – 17kb