From mboxrd@z Thu Jan 1 00:00:00 1970 From: Nick Dokos Subject: Re: Date calculations in spreadsheet Date: Sat, 04 Dec 2010 19:56:12 -0500 Message-ID: <18596.1291510572@gamaville.dokosmarshall.org> References: <87sjydxh1q.fsf@are.berkeley.edu> Reply-To: nicholas.dokos@hp.com Return-path: Received: from [140.186.70.92] (port=47743 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1PP2tm-0002lC-QM for emacs-orgmode@gnu.org; Sat, 04 Dec 2010 19:56:19 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1PP2tl-0000Ki-D5 for emacs-orgmode@gnu.org; Sat, 04 Dec 2010 19:56:18 -0500 Received: from vms173005pub.verizon.net ([206.46.173.5]:53820) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1PP2tl-0000Kb-9s for emacs-orgmode@gnu.org; Sat, 04 Dec 2010 19:56:17 -0500 Received: from gamaville.dokosmarshall.org ([unknown] [173.76.32.106]) by vms173005.mailsrvcs.net (Sun Java(tm) System Messaging Server 7u2-7.02 32bit (built Apr 16 2009)) with ESMTPA id <0LCX000MNJXO6EA0@vms173005.mailsrvcs.net> for emacs-orgmode@gnu.org; Sat, 04 Dec 2010 18:56:13 -0600 (CST) In-reply-to: Message from Ethan Ligon of "Sat, 04 Dec 2010 15:19:13 PST." <87sjydxh1q.fsf@are.berkeley.edu> List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: Ethan Ligon Cc: nicholas.dokos@hp.com, emacs-orgmode@gnu.org Ethan Ligon 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