Counting days
If you are calculating loss over a period, you will want to know the number of days, weeks or years between one date and another. There are two ways of doing this.
The old-fashioned way
Count the whole years between the first date and the last date. Remember that number (call it ‘n’).
Take the anniversary of the first date + 1 day as your new ‘first date.’
Count the days from that date to the end of the month it falls in, and write that number down.
Write a list of the whole months after the month in which your new ‘first date’ falls and before the month in which your ‘last date’ falls.
Write down the number of days in each of those months, muttering to yourself “30 days hath September, April, June and November…” as necessary.
Add up those numbers, and write down the result.
Count the days from the beginning of the month in which ‘last date’ falls until ‘last date,’ and write down that number.
Add together the total number of days in all the whole months plus the odd days in the two incomplete months, plus 365 x n.
Re-do the whole calculation to see if you get the same result. If not, keep re-doing it until you get the same result twice running.
You now have the total number of days between first date and last date. You can do all those sums if it’s really the kind of thing you enjoy. Otherwise, there’s a more sensible way:
The more sensible way
Go to timeanddate.com
Enter your start and end dates, and press ‘calculate duration.’
The original version of this post recommended using the YEARFRAC function in Excel, but this is easier. The Excel instructions have now been relegated to a comment. Many thanks to Tom Royston for the tip about timeanddate.com.
This is also a handy resource: http://www.timeanddate.com/date/duration.html
It automatically outputs time periods as days, weeks, months or years, as you desire, and is possibly more user friendly than Excel.
Many thanks for this helpful tip: I’ve rewritten the post to give it more prominence.
Here are the Excel instructions from the original version:
Put the first date in one cell, and the last in another, and click into the empty cell where you want the answer to appear. Open the toolbox and click on fx, then find YEARFRAC in the list of functions and and double-click on it.
Click on the cell that has your start date in it; then click into the ‘end date’ box in the toolbox and click on the cell that has your end date in it.
Put ‘3’ in the ‘basis’ box. (If you care why, read the help menu; otherwise just do as you’re told.)
Hit ‘return.’
I thin it is worth knowing both methods
Most of the time timeanddate.com will be easier and simple.
But if there is controversy or difficulty about the dates, and you may need to change to change one or both of them, embedding the calculation in Excel with YEARFRAC will allow for that useful automatic recalculation. This will be particularly helpful if you need to make changes in tribunal, where you won’t have internet access.
Can I also point out that the OpenOffice.Org suite will do almost exactly what Microsoft Office will do (spreadsheets, word processing etc.) but it is available for free download (no problems with copyright). The only thing is that they ask for donations.
Microsoft Office costs several hundreds of pounds (and enriches Mr. Gates to boot).
The Ooo suite will require a slight getting used to, although it is very similar in look to MSOffice. Also it is highly compatible with all versions of MSOffice and you can save in MSOffice format (if you need to send documents electronically to ET for example).
Seeing that this blog is to advise self-representing applicants, who generally have limited resources, I thought this information might be of assistance.
Also organisations wishing to make some savings should consider changing over as MSOffice will need to be updated/upgraded after a number of years. My daughters newly built school saved several hundred thousand pounds by switching to these freeware programs.
ps. I do not have any conection with the developers of the Ooo suite – just a user who converted about two years ago.