Date calculations in spreadsheet

Date calculations in spreadsheet

[Ethan Ligon]

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?

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

Thanks,
-Ethan

Re: Date calculations in spreadsheet

[Nick Dokos]

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

Re: Date calculations in spreadsheet

[Michael Brand]

On Sun, Dec 5, 2010 at 01:56, Nick Dokos <nicholas.dokos@hp.com> wrote:
> Ethan Ligon <ligon@are.berkeley.edu> wrote:
>> |------------------+--------------|
>> | Date             | Days elapsed |
>> |------------------+--------------|
>> | [2009-12-03 Thu] |              |
>> | [2010-12-03 Fri] |          365 |
>> | [2010-12-06 Mon] |            3 |
>> |------------------+--------------|
> |------------------+------------------+--------------|
> | 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>)

and then
|------------------+--------------|
| Date  start      | Days elapsed |
|------------------+--------------|
| [2009-12-03 Thu] |              |
| [2010-12-03 Fri] |          365 |
| [2010-12-06 Mon] |            3 |
#+TBLFM: $2 = date(<$1>) - date(<@-1$1>) :: @2$2 = string("")

Michael