Help - date substraction

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

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

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.

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

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.

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

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