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.

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

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.

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

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

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

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