Posts Tagged ‘Excel’

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.

2014 and 2015 Excel staff holiday planner and one page Excel calendar

Monday, December 2nd, 2013

I’ve been getting loads of requests for the 2014 versions of my staff holiday planning spreadsheet, and the one page Excel calendar. I thought it was about time I created them. And for good measure I did the 2015 version as well.

Staff holiday planner

First up here are my latest free spreadsheet that can help you track staff holidays, training, sick days and offsite days.

2014 excel staff holiday planner

They come configured 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.
It is easy to customise the formulas if you want to factor in sick days for example. See the 2012 planner for more example formulas. Note that the 2014/2015 calendar formulas count the entire row (which includes the last bit of the year before and the first bit of the year after), whereas the 2012 calendar formulas just count the 2012 year. You can adjust the formulas to suit your requirements.

As always 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 2014 staff holiday planner spreadsheet – 21kb
Download 2015 staff holiday planner spreadsheet – 21kb

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 OpenOffice (which is free). One workaround if you are forced to use Excel 97 at work is for you to use OpenOffice at home to split the spreadsheet into 2, one part for H1, and one for H2.

One page Excel calendar

As always you get the whole year in a very simple one page Excel spreadsheet.

2014 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.

2014 one page Excel calendar – 13kb
2015 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.

The 2016 versions of these spreadsheets are here: http://www.reviewmylife.co.uk/blog/2015/08/30/2016-and-2017-staff-holiday-excel-planner-and-one-page-excel-calendar/.

Batch conversion of historical foreign currency values into a native currency

Friday, June 8th, 2012

Maybe you are in the situation where you have received payments in a variety of currencies, and now you need to convert the payment values into another currency (perhaps your own native currency).

But your task is made more complicated by the fact that you need to use the exchange rate at the date the payment was received, rather than totalling up all the values in each currency and then using a single exchange rate from a specific date.

This could happen to you if for example you receive PayPal payments in a multiple currencies and want to produce a report showing how much you earnt each month. For the monthly totals to make sense you would have to have all the values in a single currency.

You could do all the conversions manually, but that would be very tedious if there are more than a few to convert. Using an Excel spreadsheet to do it for you is much more fun.

Download payment and currency data

First of all I downloaded my transaction history from PayPal. Here I’ve set it to download a year’s worth of data.

currency conversion by date 1

Then I downloaded some historical exchange rate data for the same period. I got my data from Oanda (http://www.oanda.com/currency/historical-rates/), but there are plenty of other sites offering similar data.

currency conversion by date 2

I set the ‘Currency I Want’ to be my native currency (GBP) and then used the ‘Currency I Have’ to get the currency data for each of the currencies I’ve received payments in. Which in my case is USD and EUR.

Using the spreadsheet

The spreadsheet contains some example payment data, and historical currency prices for USD and EUR so you can see how it works.

The areas that you’ll need to edit have yellow column header, and the areas with orange column headers are auto-calculated for you and shouldn’t be edited.

Download the example historical batch currency conversion spreadsheet – XLS (20kb Zip file)

Enter the payments

On the ‘Payments’ tab I entered the details of the payments received from Paypal (I filtered out any payments that I made). The data should nicely fit into the existing columns if you downloaded it as ‘Comma Deliminated – All Activity’. If your data is not from Paypal, or is in a different format you may have to re-order your columns to fit my spreadsheet.

currency conversion by date 4

The important bits to enter are the dates in column A, currency in column G, and amount in Column H.

currency conversion by date 5

You should then enter your native currency tag into cell P3. E.g. my native currency is GBP which means that I don’t want any payments received in GBP to be converted. Payments in the native currency can just be used as-is.

currency conversion by date 6

Columns L-N contain calculated values. You shouldn’t need to edit them, but you may need to copy down the formulas if you have a large list of transactions.

Enter the historical currency data

Next you should enter the historical currency data into the ‘FXRates’ tab.

currency conversion by date 3

Enter it from row 6 onwards, using a separate column for each currency. Make sure the dates for each currency match up.

Above the data in row 3 you should enter a currency tag for each currency. This tag should match the currency tag that is in column G of the ‘Payments’ sheet. E.g. you may have USD, HKD, EUR etc.

Cell A3 contains the native currency tag that you entered on the ‘Payments’ sheet, you don’t need to edit this cell.

Also don’t modify the numbers that are highlighted in orange on row 4. These are column numbers which are used to help the formulas pick the correct currency.

I use a HLOOKUP to get the column number of the currency:

=HLOOKUP(G4,FXRates!$A$3:$Z$4,2,FALSE)

And then I do a VLOOKUP to get the exchange rate. You’ll see the first condition sets the exchange rate to ‘1’ if the payment is in the native currency.

=IF(G4=$P$3,1,VLOOKUP(A4,FXRates!$A$6:$Z$10000,L4,FALSE))

Simple?

If you’d done it all correctly you should see the converted values in column N of the ‘Payments’ sheet. You can see a total in Q3.

I’d recommend you cross check a few random values to make sure that you have set the spreadsheet up correctly.

2013 one page Excel calendar

Thursday, April 12th, 2012

I’ve started getting requests for a 2013 version of my one page Excel calendar, so here it is! If you want the previous 2012 one page Excel calendar click on the link.

2013 staff holiday calendar

As always you get the whole year in a very simple one page Excel spreadsheet. There are two versions. One is plain, the other has the UK bank holidays marked.

2013 one page Excel calendar – blank
2013 one page Excel calendar – with UK bank holidays

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

The one page Excel spreadsheet calendars are zipped up. If you 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.

2013 Staff holiday planner spreadsheet

Friday, November 18th, 2011

At the time of writing it nearing the end of 2011 so some of you may be searching for my previous staff holiday planner for 2012. If you are planning very far ahead (or if you are reading this in 2012/2013) you might want my 2013 staff planner. This is my latest free spreadsheet that can help you track staff holidays, training, sick days and offsite days.

2013 staff holiday planner spreadsheet

It comes configured 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.

It is easy to customise the formulas if you want to factor in sick days for example. See the 2012 planner for more example formulas. Note that the 2013 calendar formulas count the entire row (which includes the last bit of 2012 and the first bit of 2014), whereas the 2012 calendar formulas just count the 2012 year. You can adjust the formulas to suit your requirements.

As always 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 2013 staff holiday planner spreadsheet – 23kb

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

2012 Excel week numbers calendar

Wednesday, June 15th, 2011

We are nearly half way through 2011 so you might have already started planning for 2012. This easy to read one page Excel calendar has the whole of 2012 on one page, with a good sized box for each day of the week – each day has five free cells for your use.

It has the week numbers down the side – the week numbers are written according to ISO 8601. If you work in a company and your projects are planned in terms of week numbers it can be very useful to have a calendar which lists them.

2012 excel week numbers calendar

I’ve frozen the days of the week lables at the top so they are always visible, and the months are alternately coloured to make it easy to see which month you are on.

There are two versions of this spreadsheet, one saved using the newer XLSX format, and one using the older Excel 97 compatible XLS format. Both file types can also be loaded in the free OpenOffice Calc spreadsheet if you don’t have Microsoft Excel.

2012 week numbers calendar (Excel 2007+ or OpenOffice)
2012 week numbers calendar (Excel 97+ or OpenOffice)

I also have a one page calendar for 2012, and a staff holiday planner for 2012 if you are looking for a slightly different calendar.

Amazon CloudFront and S3 maximum cost

Thursday, May 19th, 2011

When using utility computing platforms such as Amazon CloudFront, S3, EC2, or similar platforms from other companies, you are charged according to how much usage you or other people make of the files and services you publicly expose.

If you configure your files or services to be private only to you then you can easily control the cost. But when you make them accessible to the public (e.g. by hosting your website images on CloudFront) your costs are determined by people you have never met.

The costs tend to work out pretty cheap under normal usage, and these utility computing platforms are a great idea in theory. But what if the usage isn’t normal? What if someone decides to launch a DDOS attack on you by downloading huge amounts of data from your CloudFront or S3 account for example? I’ve no doubt that Amazon could handle the load, but could your bank account, or business cope?

cloud maximum cost worst case scenario

I’m not singling out Amazon here; the same applies for any ‘pay for computing power / bandwidth’ service. It is just that Amazon’s services set the benchmark for other similar companies so their platform provides a good example to base my calculations on.

The chances of such an attack happening to you are probably small, but the impact could be large, so it is worth doing the calculations before signing up.

Calculating the Amazon CloudFront worst case scenario

In order to calculate the worst case scenario we first need to understand how the pricing works. Amazon provides a page of pricing information on their website. You are charged for both bandwidth used, and the number of HTTP requests. The bandwidth cost varies depending on where in the world the data is downloaded from.

There are also some minor costs for file storage if you use S3 to store your data, and for loading the data if you use a custom origin server – but these are likely to be very minor costs so I will only use the bandwidth and number of requests for my calculations.

Next we need to know the maximum amount of bandwidth and requests that can be be used with your default account. Although it isn’t very easy to spot, there is a default limit listed on that pricing page of 1000 requests per second and 1000 megabits (megabits not megabytes – 1000 megabits = 125 megabytes) per second. That is a lot of data for any small hobby or business site but Amazon can cope with it. They even provide a request form if you need a higher limit.

Using these values you can see that in an hour the maximum number of requests is 3,600,000 and the maximum bandwidth usage is about 450 gigabytes.

The most expensive region to download data from is Japan so I will base my calculation on the data being downloaded at the maximum number of requests and bandwidth from the Japan prices (starting at $0.201/GB and $0.0095 per 10,000 requests as of May 2011). Of course in reality it would be impossible for someone to hit you with the maximum load, but that doesn’t matter – I’m calculating the theoretical worst case scenario here.

As the bandwidth pricing is tiered (as you move up the usage tiers the cost goes down) it is a bit hard to calculate the cost using a calculator. Amazon does provide its own cost calculator but I didn’t fancy using it for each data point so I’m using Excel instead. I’m using their cost calculator only to cross check my values to make sure that they are correct to an acceptable margin of error.

If you ever want to calculate tiered pricing then the SUMPRODUCT function in Excel is what will make it easy (once you figure out its cryptic syntax that is).

CloudFront worst case hour by hour cost for the first day

The first graph shows the theoretical worst case scenario cost for the first 24 hours of this imagionary DDOS attack on your account.

amazon cloudfront maximum cost per hour

After the first hour 0.45TB and 3,600,000 requests could have been charged to your account at a cost of nearly $94. By the end of the day the cost is just over $2200.

If an attack like this started how long would it take you to notice? Unless you sign up to a 3rd party monitoring service you wouldn’t find out about the problem until you next signed into the Amazon Web Services (AWS) console.

Even if you try to check everyday you can be hit with a big cost while you are asleep, on a plane, at dinner, or with friends. For a big business $2200 might be nothing, but how many hobbies website builders who are using AWS could say the same?

CloudFront worst case day by day cost for the first month

The next graph shows the maximum cost over 30 days. The subtle curve is the effect of the tiered pricing kicking in reducing the bandwidth cost each time you pass the next tier.

amazon cloudfront maximum cost per day

After a week your cost is over $11,000, at two weeks it is nearly $22,000, and after 30 days it could be over $42,000. About $40,000 of that is the bandwidth cost and only $2,000 is the requests cost. At the end of 30 days a maximum of 324TB of data could have been downloaded (theoretically).

Would Amazon spot and stop the bandwidth usage?

If this happened to you would Amazon (or similar companies) warn you?

I can’t find anything in their information that suggests they would. I’m not even sure if they’d notice. Although the bandwidth usage and number of hits might be huge for you, it’s peanuts for Amazon. Remember I’m basing these figures on their default throttled account. You can request much higher usage limits if you want.

Would you get a refund if this happened? Again I don’t know as their information mentions nothing about this. It does say that use are you are soely responsible for the use of your content, which may mean that if it is misued (e.g. DDOS attack) it is your responsibility to pay. You can understand that from Amazon’s point of view they will still incur costs for their own bandwidth whether the bandwidth usage is beneficial to you or not.

It would be good to see Amazon (and similar companies) clarifying their policies about whether they will offer any assistance in the event of an attack.

What do people pay for normal CloudFront / S3 usage?

There are many people posting about their AWS bills. Most of the stories I’ve read are about people saying how small their bill is. Here is a list of accounts I’ve found, some are using CloudFront, and some S3 (prices are mostly per month).

$0.12 – for a couple of days usage.
$0.86 – to host static files for a month.
$1.00 – a month to host static files.
$1.00 – a month at most for website data.
$1.22 – in a month for website images.
$1.30 – to make site go faster .
$1.50 – to host nutritional leaflet each month.
$1.69 – for a months image hosting to make site faster.
$1.81 – to serve blog images for a month.
$2.00 – for image hosting a month.
$5.00 – to make large site faster with CloudFront.
$5.00 – a month to make site ‘Reddit/Digg proof’.
$70.00 – a month on S3/CloudFront worked out cheaper than their dedicated server.
$440.40 – for storing images produced by a MySpace application.
$500,000 – a year saved by SmugMug by using S3.

Has this problem been mentioned before?

This problem has been mentioned many times on Amazon’s Web Services forums, so Amazon are aware of the issue, but as far as I’m aware no one has ever (until now!) produced worst case data for excessive usage.

Taking this thread as an example.

  • 2006 5th May – Someone requests feature to limit cost / bandwidth
  • 2006 5th May – Amazon reply to say that this ‘is in the works’. The plan is to enable you to cap the monthly charges.
  • 2006 – Other’s join in the conversation saying they would like the bill capping feature.
  • 2006 14th September – Amazon say the feature is ‘forthcoming’ but that they have no update on timing.
  • 2007/2008 – More people request the feature or updates from Amazon.
  • 2008 3rd June – Amazon reply saying it is still on their list of things to add but that they don’t expect to release it this year.
  • 2008 – A few more customers request the feature.
  • 2008 25th October – Amazon reply again saying it is still on the list, but they have no date to announce.
  • 2008/2009 – A few more customers request bill capping on AWS.
  • 2009 23rd March – Amazon reply and say the feature has been calendered to appear late this year or early next year.
  • 2009 3rd June – Amazon reconfirm the late this year / early next year timeframe in reply to another poster.
  • 2009/2010 – Many more replies from customer requesting some kind of cost limiting feature.

What can Amazon do?

Firstly they could make it possible to reduce the allowed bandwidth and number of hits. As the connection is already throttled, and as it is possible to increase the values, I’m sure it wouldn’t be that hard for them to allow you to reduce the limits.

And secondly they could allow you to configure a cost limit per day/week/month. Perhaps with a warning when 70% of your bandwidth is used up for example. This is what many metered web hosts do if you are near their limit.

A combination of both of these would probably be a good solution for most people who are worried about their potential bills.

Also as I previously mentioned it would be good for them to have clear public policies of what would happen if your account were hit by a DDOS attack leading to an unusually large bill.

What can I do?

If you are concerened you can add your voice to the discussions happening on the AWS forums, or try to contact Amazon directly to request these cost control features.

You can sign up for a 3rd party AWS monitoring service, which could notify you if the costs go abover a certain amount.

And finally

I’ll stress again that this problem is by no means unique to Amazon. In fact I’m not aware of any utility comuting provider that allows you an easy way to limit your costs, or throttle your usage. Therefore it won’t help you if you to change to another similar company.

I’m not saying that you shouldn’t use these services; in fact they are very useful and usually work well with good reliability. Just be aware of the risks.

Most importantly check your AWS account very regularly to monitor your costs.

Update: 11th May 2012

Amazon have now introduced automatic billing alerts. These won’t stop your charges from going up, but at least you will be notified if your pre-set limit is reached. See http://aws.amazon.com/about-aws/whats-new/2012/05/10/announcing-aws-billing-alerts/.

Update: 8th June 2012

They have now introduced a billing data API http://aws.typepad.com/aws/2012/06/new-programmatic-access-to-aws-billing-data.html.

Still no way of forcibly capping the charges to a pre-determined amount, but they are at least providing more ways to monitor the charges.

2012 staff holiday planning spreadsheet

Monday, April 11th, 2011

This staff holiday planning spreadsheet is a 2012 updated version of my 2011 staff holiday planner. This time the formulas for calculating the remaining holiday are in the default sheet – if you don’t need them you can just delete the columns.

2012 staff holiday planner xls

As before the calendar planner shows the whole year left to right, with employee name down the side. As there are 365+ columns (for the days) you’ll need a spreadsheet package like Microsoft Office 2007 or OpenOffice to view the whole year. If you try to view it with a very old package like Microsoft Office 97 the year will get truncated after 256 columns.

Week numbers are listed according to ISO 8601. The top and side areas are frozen to make navigation easier, and there are some pre-prepared categories at the top left that you can copy and paste to the relevant cells (Ctrl-c, Ctrl-v are the shortcuts). Filtering for role and staff member name is set up as well.

Download 2012 staff holiday planner – XLS 22kb

The total number of holidays for the staff member can be entered into column C, and then column D automatically shows how many holiday days are left. You can replace the formula in column D if you want behaviour different to the default.

Count full day holidays and half day holidays

This is the default formula for cell D6 which counts whole and half holiday days.

=C6-(COUNTIF(G6:NG6,”=Holiday”)+(COUNTIF(G6:NG6,”=Half”)/2))

Count holidays only

If you want to use this formula paste into cell D6 and then copy down.

=C6-(COUNTIF(G6:NG6,”=Holiday”))

Count holidays plus sick days

=C6-(COUNTIF(G6:NG6,”=Holiday”)+COUNTIF(G6:NG6,”=Sick”))

Count holidays plus sick days (with new ‘Half’ holiday value which subtracts 0.5 days)

=C6-(COUNTIF(G6:NG6,”=Holiday”)+COUNTIF(G6:NG6,”=Sick”)+(COUNTIF(G6:NG6,”=Half”)/2))

OpenOffice Err:508

If you are using OpenOffice then you need to convert the commas in the above formulas to semi-colons. e.g. =C6-(COUNTIF(G6:NG6,”=Holiday”)) would change to =C6-(COUNTIF(G6:NG6;”=Holiday”)). Formulas that are in the default spreadsheet will work, this just applies to newly added formulas.

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.