Re: Date calculations in spreadsheet

[Nick Dokos <nicholas.dokos@hp.com>]

Ethan Ligon <ligon@are.berkeley.edu> wrote:

> I'm working with a small spreadsheet, and would like to know how to
> manage date calculations within the spreadsheet.  For example,
> 
> * How to do date calculations in a spreadsheet?
> |------------------+--------------|
> | Date             | Days elapsed |
> |------------------+--------------|
> | [2009-12-03 Thu] |              |
> | [2010-12-03 Fri] |          365 |
> | [2010-12-06 Mon] |            3 |
> |------------------+--------------|
> 
> The question: What's the simplest way to construct a column formula for
> the second column to deliver the indicated results?
> 

In the format above, there are problems that have nothing to do with dates:
you'd need conditional code to distinguish between the first row and later
rows. So I reorganized your table a bit in order to illustrate the date calculation:

--8<---------------cut here---------------start------------->8---
* How to do date calculations in a spreadsheet?
|------------------+------------------+--------------|
| Date  start      | Date end         | Days elapsed |
|------------------+------------------+--------------|
| [2009-12-03 Thu] | [2010-12-03 Fri] |          365 |
| [2010-12-03 Fri] | [2010-12-06 Mon] |            3 |

#+TBLFM: $3 = date(<$2>) - date(<$1>)
--8<---------------cut here---------------end--------------->8---


> And finally, my usual obligatory apology for not noticing the exhaustive
> thread on exactly this topic that I've undoubtedly missed. 8^)
> 

Well, after this I felt duty bound to find the thread:

   http://thread.gmane.org/gmane.emacs.orgmode/7741

Actually, it's only one of the threads on date calculations and is not
exhaustive, but what the hey ;-)

Many thanks to Chris Randle for coming up with the original solution. I
have now used his answer some half a dozen times to answer questions on
the list: the gift that keeps on giving, to coin a phrase...

Nick