Difference between two date | Newbie | | Join Date: Jul 2007
Posts: 8
| | |
Hi,
I have a big problem with Date function in Excel.
I need to find out the difference between two date.
Suppose, one baby was born in 10th Feb 2000.
What will be his age now (Yr- Month-Day).
Please help.
|  | Site Moderator | | Join Date: Apr 2007 Location: Texas
Posts: 5,435
| | | re: Difference between two date
Heya, Tapan.
I'm going to go ahead and move this thread to the Windows forum, where one of our resident Experts will be more likely to see it.
|  | Moderator | | Join Date: Sep 2006 Location: Minden, Nevada, USA
Posts: 6,400
| | | re: Difference between two date
Quote:
Originally Posted by pbmods Heya, Tapan.
I'm going to go ahead and move this thread to the Windows forum, where one of our resident Experts will be more likely to see it. Sorry, pbmods. The Windows Forum is in the Sys/Admin Group. Excel questions have been relegated to the Misc. Q.s Area. I'm moving it back there.
| | Site Moderator | | Join Date: Apr 2007 Location: Texas
Posts: 5,435
| | | re: Difference between two date
Quote:
Originally Posted by bartonc Sorry, pbmods. The Windows Forum is in the Sys/Admin Group. Excel questions have been relegated to the Misc. Q.s Area. I'm moving it back there. Very well then. We'll keep Office-related q's in Misc.
Tapan, take a look at the date and time functions in Excel.
| | Newbie | | Join Date: Jul 2007
Posts: 8
| | | re: Difference between two date
Hi,
I have gone through all Date Functions available in Excel.
But I did not get my answer.
The Year Function shows no. of Years in between two dates.
The Month Function shows no. of Months in between two dates.
The Day Function shows no. of Days in between two dates.
But I unable to get the Difference between two Dates expressed in Yr.-Month- Date.
Example:
Date 1=10th Feb 2000
Date 2=9th July 2007
Difference = 7 yrs. 4 Months 29 Days
I did not find any formula to solve this problem.
| | Site Moderator | | Join Date: Apr 2007 Location: Texas
Posts: 5,435
| | | re: Difference between two date
Heya, Tapan. Quote:
Originally Posted by MSDN =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)<M ONTH(A1),DAY(A2)
>=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<D AY(A1)),-1)))&" months,
"&A2-DATE(YEAR(A2),MONTH(A2)-IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&" days" This is mostly what you need. Play around with it until you get the result you're looking for.
| | Newbie | | Join Date: May 2007
Posts: 1
| | | re: Difference between two date
Try =((YEAR(A2-A1) - 1900) & " Years, " & (MONTH(A2-A1)) & " months, " & (DAY(A2-A1)) & " days")
Where A1 is the first date and A2 is the second date
| | Newbie | | Join Date: Jul 2007
Posts: 8
| | | re: Difference between two date
Hi,
conorb66
Your answer is showing 1 months extra.
and the answer which is taken from MSDN quite ok but
this formula is showing certain discripency when there will be
a leap year.
Otherwise everything is fine.
Very very thanks for your co-operation.
| | Newbie | | Join Date: Jul 2007 Location: gapan city, philippines
Posts: 4
| | | re: Difference between two date
Quote:
Originally Posted by Tapan Bera Hi,
I have a big problem with Date function in Excel.
I need to find out the difference between two date.
Suppose, one baby was born in 10th Feb 2000.
What will be his age now (Yr- Month-Day).
Please help. Simply type date of birth and then subtract it on the current system date. format cell address to date.
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,376 network members.
|
