Help - date substraction

[TAKTOUKA]

Kindly I need a formula to calculate number of days between two dates over a year apart using excel

[dominicb]

Good evening Taktouka

Type in cell A1 the date 01/01/2004 and in cell B1 the date 18/05/2005. In cell C1 put the formula:

=B1-A1

this (as long as it is formatted as a number) will return 503 (ie., days).

HTH

DominicB

[TAKTOUKA]

Hi Dominique,

Thank you for your help but I need to calculate the age of a certain people using the excel
I need the answer in day, month, year. I ve the date of birth and i want to calculate the age.

[mangesh_yadav]

One way is to format the result as custom and then enter "yy m d"

or simply use the following formula in your cell
=TEXT(B1-A1,"y m d")

- Mangesh

[TAKTOUKA]

By formating the result as custom and then enter "yy m d"

Difference between date of birth ( 23 / 1 / 1977) and as of today ( 19 /5 / 2005) is 28 years 4 months and 25 days which is not accurate, the right answer is 28 years 3 months and 26 days.

Could you please help solving this.

[mangesh_yadav]

Sorry, my mistake. Thats not correct actually.

Try this instead:

=YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1) & " " & MONTH(A2)-MONTH(A1)-IF(DAY(A2)>=DAY(A1),0,1) & " " & IF(DAY(A2)-DAY(A1)<0,DAY(DATE(YEAR(A2),MONTH(A2),1)-1)+(DAY(A2)-DAY(A1)),DAY(A2)-DAY(A1))

Not fullly tested

A1 = start date
A2 = end date

- Mangesh

[mangesh_yadav]

A slight varaition:

=YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1) & " " & 12-(MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1) & " " & IF(DAY(A2)-DAY(A1)<0,DAY(DATE(YEAR(A2),MONTH(A2),1)-1)+(DAY(A2)-DAY(A1)),DAY(A2)-DAY(A1))


- Mangesh

[TAKTOUKA]

Wrong formula still trying to get it. Thank you for your help

[mangesh_yadav]

can you give an example where it is failing

Mangesh

[mangesh_yadav]

Try:

=YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1) & " " & (MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0) & " " & IF(DAY(A2)-DAY(A1)<0,DAY(DATE(YEAR(A2),MONTH(A2),1)-1)+(DAY(A2)-DAY(A1)),DAY(A2)-DAY(A1))


- Mangesh

[TAKTOUKA]

Did you test the last formula????

[TAKTOUKA]

Did not get it yet

[TAKTOUKA]

Still need help

[mangesh_yadav]

Give me the cases where it is not working. As for me the formula works fine enough atleast for these 4 different types of cases:

15-Apr-76 | 20-May-05 | 29 1 5
23-Jan-77 | 19-May-05 | 28 3 26
23-May-04 | 19-Jan-05 | 0 7 27
19-May-04 | 23-Jan-05 | 0 8 4

Note: The result is displayed in Years Months Days

Mangesh

[mangesh_yadav]

This seems to give me the answer for a lot more cases now. Bit long though. Will try to see if I can shorten it.

=YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1) & " " & (MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0) & " " & A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0)),DAY(A1)))+IF(A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0)),DAY(A1)))<0,DAY(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0)),DAY(A1))),0)


- Mangesh

[mangesh_yadav]

After further finetuning:

=YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>MONTH(A1),0,1) & " " & (MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<=MONTH(A1),12,0) & " " & IF(DAY((DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0)),DAY(A1))))=DAY(A1),A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0)),DAY(A1))),DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-DAY(A1)+DAY(A2))


- Mangesh

[TAKTOUKA]

Hi Mangesh,

I am a new user of excel, really thank you for your fruitful help.

To determine the age of a person. If in cell "A3" you enter the date of birth, and in cell "B3" today's date, the following formula in "C3" would give you a good approximation of the age (plus or minus a few days):
=INT((B3-A3)/365) & " years and " & TRUNC((MOD((B3-A3);365))/30) & " months"

Would you please help me to have an accurate age with days too.

All the best.

[mangesh_yadav]

Hi TAKTOUKA,

I did not continue on the formula you gave as it has inherent bugs when you use the hard-coded numbers 365 and 30. An approximation is very easy. The whole exercise is done basically to approach at a valid formula given a certain set of rules.


Try the following formula:

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


with A1 start date and B1 end date


Check this thread for a discussion on the above:
http://excelforum.com/showthread.php?t=372455&page=2

- Mangesh