Start Date minus (Years + Months + Days) = ?

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" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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
>
>