Blog

Spreadsheet Blog

Friday 30 July 2010
What's the difference? So now you've got your date and time, what are you going to do with them? A common enough requirement for dates is to calculate someone's age. The current date (as above) can be obtained using the TODAY() function so if we have a valid date, say, 2/5/1982 in cell A1 and we set A2 to be =TODAY()-A1 we get a rather large number, 10316, which is in fact the age of a person born on 2/5/1982 but expressed in days!
		Since it is more common to express a person's age in years, we can achieve that thus: Set cell A2 to be = YEAR(TODAY()) - YEAR(A2). This takes the year of the date in cell A2 (1982) away from the year of the current date (2010) and with a bit of luck and a fair wind you should get 28, which is more like it. In this way you can calculate the difference between any 2 dates. In the same way as YEAR( gives you the year of a date, MONTH( will give you the month, and I'll leave you to guess at what DAY( will provide!
When calculating the difference between days, be careful to strip out the time, if still attached, as this can distort the number of days. Bear in mind that 23:59 on 29/7 is only 2 minutes away from 00:01 on 30/7. if you're counting days your result will be 0 rather than the 1 you'd expect. To get rid of the time, use the INT( function ('INT(' is short for integer which is the whole part of a number).
		While calculating the difference between dates is reasonably straightforward, times can be a little more challenging, not because of the Excel functions but because of the way we work.
If someone starts their working week at 9:00am on Monday 26/7 and ends it at 5:00pm on 30/7 they will not have worked 128 hours (well, not anywhere I've ever worked) so simply counting the hours between their start time and end time will not give the right answer. You have to allow for the working day, i.e. 9-5 on Monday, 9-5 on Tuesday, etc. which will yield 40 hours.
		Subtracting one time from another will give an answer expressed as a fraction of a day. Thus, 17:00 - 09:00 will give 0.3333333. If you multiply that by 24 (hours in a day) you get 8 which is the correct number of hours. 17:30 - 09:00 gives 0.35417, which, multiplied by 24, gives 8.5. if you take off the whole number of hours and multiply by 60, you convert the fraction of an hour to minutes. To remove the whole number use the formula =X-INT(X). Ah! Time for a cup of tea!

Monday 19 July 2010
Take your time with dates A common problem encountered particularly where a spreadsheet is created as part of a download from an external database is that of working with dates, which are commonly in the Date/Time format ‘19/07/2010 07:24:38’. Where they are downloaded as an actual date and time, they are easily managed, but if simply text, they are a bit more fiddly, but far from impossible.
		Excel is pretty resilient with dates and if it even remotely recognises what you have as a date, you’ll be fine. Start by trying to add 1 to your date (i.e. if your date is in cell A1, in another cell enter ‘=A1+1’. If the value you get is the date in A1 incremented by 1 day (i.e. 19/07/2010 07:24:38 becomes 20/07/2010 07:24:38) then all is well and what you have is a date. If, however, you get ‘#VALUE’ then you have text. At least now you know!
Dates and times in Excel are stored as numbers with the date as the whole number and the time a fraction. The whole numbers (integer) are calculated from 31/12/1899 (Don’t ask!) so that day 1 is 1/1/1900, which makes 19/7/2010 40378. The beauty of this kind of simplistic approach is that if you take 40378 from 40379 you get 1 day which is right.
		Times are expressed as fractions of a day with 12 noon 0.5 and 06:00am 0.25 and 18:00pm 0.75. To convert a time to hours multiply it by 24. Thus 0.5 becomes 12. However 0.52083333 multiplied by 24 is 12.49999999, which implies it is not an exact number of hours. The hours can be removed with the formula =X-INT(X) which in our example gives us 0.49999999. If that is now multiplied by 60 to give us a number of minutes, we get 30, so that 12.5208333 is 12.30pm.
So to deconstruct a date/time value, obtain the date by taking the integer of the value (i.e. =INT(X) and then isolate the time as above. This can then be broken down into hours and minutes. To reconstruct a date/time field from a date and time, first add the minutes/60 to the hours and then the hours (including the converted minutes) /24 to the date.
		If after all this your date/time value is just text (see above), then you can split them as follows (value in cell A1): Set cell A2=FIND(" ",$A4). This will find the space in the middle of the value. Set A3=LEFT($A4,$C4-1) for the date and A4 =MID($A4,$C4+1, LEN($A4)) for the time. (The function LEFT( gives the leftmost portion of a text string and MID( gives the portion of the string starting in the middle from the character specified.) More on dates and times next week.

Monday 12 July 2010

Take another look at 'Copy' and 'Paste'

Once, while working on a project at a large organisation, a colleague asked me to email her an extract of one of my spreadsheets for a meeting she was having later that day. Not long after I did so she called me back to say there was a problem with the spreadsheet. I realised that the problem was caused by formulas in one of the columns going wrong because the full data set was not present on her version.

I asked her to select the column and then said to go to 'Edit' then 'Copy'. Immediately she turned on me saying angrily, "I'm not a fool, you know. I can 'Copy' and 'Paste'. Calmly, I said, "So you know about 'Paste special', do you?

Sheepishly, she said she did not. I then explained to her how to get rid of the formulas using Paste special (see below).

Most people can use Copy and Paste (and their shortcuts CtrC and CtrV) pretty comfortably but while many may have heard of 'Paste special', most have not tried it.

When you copy a cell, you pick up lots of information. You copy the value, any underlying formula, the format, any comments that might be on the cell, any validation, etc. and when you paste, you get the lot.

There are often situations where you neither want nor need the lot. It may be, for example, that you want to copy a number but not change the formatting of the destination cell. To do this copy the number but on the destination cell, press 'Paste special' then select 'Value'. The number you copied will appear in the destination cell but still formatted as the previous value.

You can also use it, for example, to generate a series. In cell A1 enter a '1' then in A2 put '=A1+3'. Drag cell A2 downwards and you will have a series of numbers incrementing by 3. Select cell A1 down to the last filled cell in column A then go to 'Edit' then 'Copy' then without moving off column A, click 'Edit' again then 'Paste special' then select 'Values' then press 'OK', the formulas you entered will be changed to the values shown.

It can also be used to transpose data (i.e. convert a column to a row or vice versa). Select column A again then 'Edit' 'Copy' then go to Sheet 2 then select cell 'A1', click 'Edit' then 'Paste special' then click transpose, then 'OK'. The numbers will now run across the row rather than down the column.

Create a new workbook and play around with 'Paste special'. You never know where it may lead.

For more information please contact us on help@bossml.com