Formula for difference in two dates

Discuss the spreadsheet application

Formula for difference in two dates

Postby apsarv » Thu Feb 05, 2009 9:04 am

Hi I need a Formula for difference in two dates for the whole column.
The first date is entered in A column and second date in B column and I need the result in C column.
There are around 1000 such entries so it goes from A1 - A1000. So, I was thinking if I can get one formula for the whole column.

ANother problem I am having is that MS Excel formulas are not working in calc.. :o :cry:

I am working in dd/mm/yyyy format
OOo 2.3.X on Ms Windows XP
apsarv
 
Posts: 2
Joined: Thu Feb 05, 2009 8:50 am

Re: Formula for difference in two dates

Postby Villeroy » Thu Feb 05, 2009 10:27 am

=B1-A1 gets the difference between two numbers in unit days. Works in all spreadsheet including Excel.

ANother problem I am having is that MS Excel formulas are not working in calc

Of course 99% of all Excel formulas do work when you simply load your Excel file into Calc.

I am working in dd/mm/yyyy format

Number formats have no influence on the values nor calculations.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17319
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formula for difference in two dates

Postby apsarv » Mon Feb 09, 2009 7:38 am

Hi
Thanks for that but I need the formula to get the output in years, months and days.

I use this formula in Excel but its not working in Calc.

=YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<MONTH(A1),AND(MONTH(A2)=MONTH(A1), DAY(A2)<DAY(A1))),1,0)&" years, "&MONTH(A2)-MONTH(A1)+IF(AND(MONTH(A2)<=MONTH(A1),DAY(A2)<DAY(A1)),11,IF(AND(MONTH(A2)<MONTH(A1),DAY(A2)>=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<DAY(A1)),-1)))&" months,"&A2-DATE(YEAR(A2),MONTH(A2)-IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&" days"


and as I need it for the whole column so I thought if I could find a simploer solution than this formula...
OOo 2.3.X on Ms Windows XP
apsarv
 
Posts: 2
Joined: Thu Feb 05, 2009 8:50 am

Re: Formula for difference in two dates

Postby FJCC » Mon Feb 09, 2009 7:53 am

It is too late at night for me to think of another formula, but the one you have seems to work if you change all of the commas to semicolons (except within your literal strings such as "years, ". At least I tried a few variations and the answer was correct.
AOO 3.4 or 4.0 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 3686
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Formula for difference in two dates

Postby Villeroy » Mon Feb 09, 2009 9:30 am

Excel has (undocumented) DATEDIF, in Calc you can use additional functions MONTHS and YEARS.
http://wiki.services.openoffice.org/wik ... y_category

Both applications don't know the respective other application's function, but there are compatible formulas made of common spreadsheet functions:
Re: [Solved] Replacing DATEDIF in an Excel equation? (very similar to your functions).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17319
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Villeroy and 28 guests