In Excel, suppose I have a regular date, like: 3 February 1922

Now, I want to subtract: 86 years, 6 months, and 23 days.

1) How can I do this in a formula?
2) What is the correct answer?
3) What are the limitations in Excel for this type of calculation?

Thanks much in advance.

Assuming you have 03/02/1922) in A1
86 in C1
6 in D1
23 in E1

1) The formula is:
=DATE(YEAR(A1)-C1,MONTH(A1)-D1,DAY(A1)-E1)

2) The correct answer cannot be calculated by Excel (see limitations)

3) Excel computes dates as an integer number of days since 01/01/1900

HTH
--
AP

"quartz" a écrit dans le message de news:
...
In Excel, suppose I have a regular date, like: 3 February 1922

Now, I want to subtract: 86 years, 6 months, and 23 days.

1) How can I do this in a formula?
2) What is the correct answer?
3) What are the limitations in Excel for this type of calculation?

Thanks much in advance.

You could get the correct value in VBA and output it to a cell as text
though as the VBA dates start in the year 100. Code follows (minus
robustness & error handling)

Sub Test()

Debug.Print SubtractTimeFromDate(DateSerial(1922, 2, 3), 86, 6, 23)

End Sub

Public Function SubtractTimeFromDate(startDate As Date, years, months,
days) As String

Dim endDate As Date

endDate = DateSerial(Year(startDate) - years, Month(startDate) -
months, Day(startDate) - days)

SubtractTimeFromDate = Format(endDate, "dd/mm/yyyy")

End Function

This works...thanks.

"Barry-Jon" wrote:

You could get the correct value in VBA and output it to a cell as text
though as the VBA dates start in the year 100. Code follows (minus
robustness & error handling)

Sub Test()

Debug.Print SubtractTimeFromDate(DateSerial(1922, 2, 3), 86, 6, 23)

End Sub

Public Function SubtractTimeFromDate(startDate As Date, years, months,
days) As String

Dim endDate As Date

endDate = DateSerial(Year(startDate) - years, Month(startDate) -
months, Day(startDate) - days)

SubtractTimeFromDate = Format(endDate, "dd/mm/yyyy")

End Function