Book

Suggestions


Enter your email address:

Delivered by FeedBurner


Home Page

Bloglines

1906
CelebrateStadium
2006


OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal












Subscribe here
Add to 

My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!


eXTReMe Tracker
  Web http://www.klippert.com



  Thursday, November 20, 2014 – Permalink –

Date Arithmetic

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."

=DATEDIF(EarliestDate,LatestDate,Interval)

=DATEDIF(A2,A1,"d")


Here's THE source for date math:
Chip Pearson:
All About Dates
Also:

John Walenbach:
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

Labels: , , , , , ,


<Doug Klippert@ 3:46 AM

Comments: Post a Comment