how to subtract date in excel

sir,

i want to subtract two date in excel 2007.

31-12-2014 - 15-02-1980  = result 16-10-34

how can do this what is the formula for this

Thanks
ManojtanwarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
just put minus in between, see attached.
Book1.xlsx
Roy CoxGroup Finance ManagerCommented:
I think this is what you want

=DATE(YEAR(A3)-YEAR(B3),MONTH(A3)-MONTH(B3),DAY(A3)-DAY(B3))

Assumes A3 contains the latest date - 21/12/2014
B3 contains the earliest date - 15/02/1980

Result formatted as date = 16/10/1934

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Danny ChildIT ManagerCommented:
alternatively, use the DATEDIF function:
=DATEDIF(B1,A1,"y") & " years, " & DATEDIF(B1,A1,"ym") & " months, " & DATEDIF(B1,A1,"md") & " days"
if your 1980 date is in A1, and 2014 date in B1

Gives the slightly clearer format of:
34 years, 10 months, 16 days
which cannot be mistaken for an actual date - ie 16th Oct 1934.
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

ProfessorJimJamMicrosoft Excel ExpertCommented:
I would not use DATEDIF with its known bug

the bug is seen not all the time but in some specific date calculations.

for example if you calculate number of days between 1/31/2012 and       3/1/2012  the abovementioned DatedIF function will drop 0 years, 1 months, -1 days   while in reality it should be month and one day  this formula =YEAR(B1)-YEAR(A1)-(TEXT(B1,"mmdd")< TEXT(A1,"mmdd"))&" years "&MOD(MONTH(B1)-MONTH(A1)-(DAY(B1)< DAY(A1)),12)&" months "&B1-MIN(DATE(YEAR(B1),MONTH(B1)-(DAY(B1)< DAY(A1))+{1,0},DAY(A1)*{0,1}))&" days"  will show the correct result.   Original Author of this formula is barry houdini  who is the "Master of Date Formulas"

my statement is also backed by Rick excel MVP in his blog

and formula alternative by Barry here


besides your question on this thread is/was how to subtract one date from another to get another Date as result and your question was Not, "How to calculate years, months, days"
Roy CoxGroup Finance ManagerCommented:
I agree with ProfessorJimJam, the original question was to subtract a date from another date and return a date, not calculate the time between. A very misleading question.
Danny ChildIT ManagerCommented:
I think the unusual format of the question resulted from some misunderstanding of how Excel can manipulate dates.
I was lucky in that my format of answer seemed to meet the OP's needs, but I completely accept that the comments from ProfessorJimJam and Roy Cox are valid.

To modify Roy's solution, but to use my output format, and also avoid the DateDif bug would need this:
=YEAR(B1)-YEAR(A1)&" years, "&MONTH(B1)-MONTH(A1)&" months, "&DAY(B1)-DAY(A1)&" days."
Danny ChildIT ManagerCommented:
Requesting to the Mods for a change of points of all of them to go to Roy Cox, as his answer is more stable than mine.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.