Boston Linux & Unix (BLU) Home | Calendar | Mail Lists | List Archives | Desktop SIG | Hardware Hacking SIG
Wiki | Flickr | PicasaWeb | Video | Maps & Directions | Installfests | Keysignings
Linux Cafe | Meeting Notes | Blog | Linux Links | Bling | About BLU

BLU Discuss list archive


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Open Office vs. Excel - dates



 Jerry Feldman wrote: 
> A1 = 06/04/2002 
> B1 = 07/04/2007 
> B2 = =DATE(YEAR(B2); MONTH(B2) + 1; DAY(B2)) 
> Replicated down through B69 

Shouldn't that be: 
B2 = =DATE(YEAR(B1); MONTH(B1) + 1; DAY(B1)) 

Assuming the intent was to start with 07/04/2007 and increment by one month. 


> C2 = =YEARS($A$2;$B2;0) 
> Replicated down through C69 
> 
> In Excel, the formula in C2 (and following) is: 
> C2 = =DATEDIF($A$2;$B2;"y") 

I'm not following precisely what you're trying to do as you didn't 
define what was in A2, but try: 

=YEAR($B2 - $A$2) 

Though instead of getting 2 for a 2 year delta, you might get 1902, 
depending on the age of your spreadsheet. 

The underlying date serial number follows a documented format (I believe 
the integer portion is in days), so you could simply do: 

($B2 - $A$2)/365 

and apply an appropriate format, or apply a rounding/truncating function. 


> The problem is that OOo does not recognize the DATEDIF function and 
> Excel does not recognize the YEARS function. 

I have a copy of Excel 97 and it has neither. :-) 

  -Tom 

-- 
Tom Metro 
Venture Logic, Newton, MA, USA 
"Enterprise solutions through open source." 
Professional Profile: http://tmetro.venturelogic.com/

-- 
This message has been scanned for viruses and 
dangerous content by MailScanner, and is 
believed to be clean. 

_______________________________________________ 
Discuss mailing list 
[hidden email] 
http://lists.blu.org/mailman/listinfo/discuss
 


BLU is a member of BostonUserGroups
BLU is a member of BostonUserGroups
We also thank MIT for the use of their facilities.

Valid HTML 4.01! Valid CSS!



Boston Linux & Unix / webmaster@blu.org