EARLIER FEATURES
FEATURES CONTENTS
LATER FEATURES
|
|
|
|
| 7th April 2002 | MEN ARE FROM MARS, |
Brian Grainger |
|
It started innocently enough. My work colleague asked when the next leap year was. Strange I thought, but thought nothing more of it. Then a little later on he asked me to have a look at his Excel 2000 spreadsheet! He had a couple of columns of dates. The first column was a start date. The second was an end date. In a third column he was trying to calculate the number of days between the two dates. Simple enough you would think. Then I could see his problem. He had kept the end date fixed and varied the start date from February 27th through to March 3rd consecutively. One would expect the third column to show the number of days decreasing by one each time. Unfortunately there was a sudden jump of two days at one point. I could now see why he was asking me about leap years. He thought the extra day in February was the problem. However, his dates were in the year 2006 so this was not the reason. He made the customary oath to Microsoft and then I had a look at his third column formulae. He was using a function I had never heard of, DATE360. A quick look through the help file showed that this calculated the number of days between two dates - just what he wanted. However, in the small print it said assuming a year of 12 equal months of 30 days each! After questioning who would want to use a year of 360 days, I decided this was the root of the problem. According to Microsoft, deeper in the help file, this 360 day year is used by some Accountants. Don't they live in the real world, like the rest of us? This did not solve the problem. The question was how to calculate the number of days between two dates in Excel. I consulted a book I have in my drawer at the moment, 'Office 2000 Secrets'. I would recommend this to any expert who wishes to use Microsoft Office 2000. It is not for the novice, however. In this case, all the book could offer was a reference to a Visual BASIC function - datediff - which did indeed do the necessary. However, using Visual BASIC is not something I undertake lightly. There must be another way. Something was nagging me at the back of my mind. Had I read something about an undocumented Excel function called DATEDIFF. I asked my colleague to try DATEDIFF(<start date>, <end date>) but that came up with an error suggesting the function did not exist. When all else fails consult the help file. Up popped the answer wizard so, not expecting much success, I asked: How do I calculate the number of days between two dates? Up popped a list of items that, for a change, was unusually short! It suggested DAYS360 and DATEDIF. (Note:- This only works in Excel 2000. In Excel 97 it offers DAYS360 and NETWORKDAYS, despite the fact that DATEDIF is in Excel 97 as well.) Bingo! We knew the first function was not the one to use and the second one confirmed my memory had not been playing tricks with me. Nice of Microsoft to give the Excel function one less 'f' in the name to that of the Visual BASIC function. By consulting the entry for DATEDIF I determined the syntax was: DATEDIF(<start date>,<end date>,<type>) Where <type> was: Note that <type> must be a string value. Typing d, m or y is not enough. It must have the quotes. This is Microsoft's way of making sure you do not stumble on the answer by accident - you must know! The other thing to realise when using this function is that the number of days does NOT count both the start and end date in the period. e.g. DATEDIF(27 February 2002, 5 March 2002,"d") equals 6, not 7. This story of Microsoft secrets does not end there. When I got home I consulted my Excel 5 manuals. These were the paper things Microsoft used to supply when they really cared about their users. This told me I could calculate the number of days between two dates by simply subtracting the dates! I had actually started solving the problem by trying this approach but when I found, 5 March 2000 - 27 February 2002 = 6 January 00 I did not pursue it further. The trick is to make sure the result cell is formatted as a number. Excel will think, because you are subtracting two dates, that it is a date and format the cell accordingly. Hence 6 comes displayed as the 6th day in its in-built calendar! What conclusions can we draw from this tale?
|
|
|
|
|
|
|
|
|
|
|
