Snoozing at the OASIS
Posted on 28 Jan 2009 at 17:07
This month, Simon Jones looks into the maintenance of the ODF standard, and sizes up the new Outlook add-in Xobni.
DateDif() calculates the number of days, months or years between two dates, and its syntax is DateDif(startdate, enddate, unit). The start and end date parameters can be text strings, date values or references to cells containing date strings or values. The unit parameter must be a text string, or cell reference to a text string that determines what information is returned.
DateDif() is only included in Excel and SharePoint for compatibility with Lotus 1-2-3, and its results are readily attained by using straight subtraction combined with the Year() and Month() functions, so it doesn't really add anything useful. Since dates are stored in Excel as days elapsed since 01/01/1900, the number of days between any two dates is just one date subtracted from the other. The number of years between two dates is similarly easy to work out by subtracting the results of the Year() function on both dates, for example =Year(D2)-Year(D1). The number of months between two dates is 12 times the number of years plus the number of months, for example =(Year(D2)-Year(D1))*12+Month(D2)-Month(D1). This works even if the month of the end date is earlier than the month of the start date, so May 2002 from March 2006 gives four years plus minus-two months, in other words three years and ten months, or 46 months.
The "YM" variant of the DateDif() function is just the subtraction of the month values, as in =Month(D2)-Month(D1), and the "MD" variant is similarly just the subtraction of the Day values as in =Day(D2)-Day(D1). The "YD" variant is achieved by first calculating the number of days between each date and the start of its year, and then subtracting the results, so =(D2-Date(Year(D2),1,1))-(D1-Date(Year(D1),1,1)).
If you want to ignore weekends and other holidays use the NetWorkDays() function, which takes two dates and, optionally, a range of holiday dates then calculates the number of working days between them. Unfortunately, all versions of Excel produced so far assume that the weekend days are Saturday and Sunday, which makes it useless in the Middle East, where the weekend days are either Thursday and Friday or Friday and Saturday (Iranians only get Friday off, generally working Saturday to Thursday). This cultural short-sightedness is likely to be fixed in the next version of Excel, as it was one of the shortcomings highlighted by the ISO when it was standardising the OOXML file formats.
There's also a YearFrac() function that can be useful - it returns a floating-point number representing the number of years between two dates. This function takes two dates as parameters and an optional Basis number, which changes how it counts days per month and days per year. The true fraction of a year is obtained by setting Basis=1, which uses the actual number of days for each month and days per year from the date parameters. However, some financial and statistical users will require fictions, such as that there are 30 days in every month or 360 or 365 days in every year, thus smoothing out the irregularities of the actual calendar - these are achieved by four other Basis values, as shown in the table 2. The NASD mentioned under Basis 0 is the US National Association of Securities Dealers - since succeeded by the US Financial Industry Regulatory Authority - and is the same NASD that lent its name to the NASDAQ stock exchange. See http://en.wikipedia.org/wiki/Day_count_convention for more details of the different conventions.
From around the web
lookeen for the best search
I use lookeen! unbelievable fast, very accurate, great handling and lots of filters and functions. just to mention some features: as lookeen being an outlook add-in, you can search very comfortable your mails plus attachments in multiple (!) PST-files. lookeen is compatible withe microsoft exchange servers and offers the use of an shared index (to spare system resources)...no other prog offers so many features.
www.lookeen.net
By JudginD on 26 Nov 2009 ![]()
advertisement
- Why virtualisation hasn't slowed the growth of data
- How to make Google AdWords work for your business
- The curse of sloppily written software
- Paying for your crimes with Bitcoin
- Behind the scenes: tech support for Formula 1
- The security risk of fat fingers
- Why Windows Phone 7 isn't quite ready for business
- When will Microsoft stop fiddling with Windows 8?
- Flash down the pan?
- Metro Style apps vs desktop applications
- Chrome's shine getting lost in translation
- BytePac: the cardboard hard disk enclosure
- How tech loosens our grip on reality
- Hokum watch: Safer Internet Day
- Why I'm deleting Adobe from my PC
- Prepare to be patronised: it's Safer Internet Day
- Dear Sony, Samsung and every other tech company in the world: stop trying to be Apple
- Will Apple's Final Cut Pro X update placate the pros?
- Smartr Contacts for iPhone review
- Switching to Office 365's Outlook Web App
- VeriSign slammed for security breach cover-up
- SAP willing to share HANA with Oracle
- Why using a tablet could harm your health
- New RIM boss: no need for drastic change
- RIM founders fall on their swords
- Slow economy helps boost Red Hat revenue by 23%
- Google+ pages get multiple admins
- One in five companies lack card industry compliance
- Oil industry warns hacking attacks could kill
- British workers fear email monitoring
advertisement

