Thursday, November 20, 2014 – Permalink –
The drunken cousin
Working with dates has a few twists.
Excel believes that time began on January 1, 1900.
Each day since then is counted so that September 1, 2003 in Excel-speak would be → 37,865.
9/1/03 7:33 A.M. is a decimal → 37865.31458333333
When you subtract one date from another, for instance 9/1/2003 (A1)minus 7/4/2001 (A2),
Excel displays the odd answer of → 2/27/1902.
Excel formats the result of a formula with the same format as the source cells,
Right-click the formula cell (=A1-A2).
Select Format Cells ..., and then choose a Number format with zero decimals.
The correct number of days → 789 will now be displayed.
Another way is to use the rarely documented DATEDIF function. Chip Pearson calls it "the drunken cousin of the Function family."
Here's THE source for date math:
All About Dates
Extended Date Functions Add-In
"Many users are surprised to discover that Excel cannot work with dates prior to the year 1900. The Extended Date Functions add-in (XDate) corrects this deficiency, and allows you to work with dates in the years 0100 through 9999."
MS Knowledge Base:
How To Use Dates and Times in Excel
See all Topics excel
<Doug Klippert@ 3:46 AM
Comments: Post a Comment