# 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
###### Who is Participating?
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.

Commented:
just put minus in between, see attached.
Book1.xlsx
0
Group 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
0

Experts Exchange Solution brought to you by

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

IT 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.
0
Commented:
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"
0
Group 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.
0
IT 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."
0
IT 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.
0
###### 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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.