date column based on existing date column

[Michael Malinsky]

If your end date is in A1, then in B1 use:

=a1-365

 

[Spence]

What I need to do is simple (I hope.) I have a column
called END DATE and I need to generate another column
START DATE based on the END DATE records. Each record in
the START DATE column needs to be 365 days earlier than
the date in the existing END DATE record. So, I'm looking
for something like this:

END DATE START DATE
12/04/2005 12/03/2004
01/25/2004 01/24/2003


I'm hoping there's an easy function to save me from having
to enter all these start dates manually. Any help is
appreciated.

Thanks,
Spence

 

[Frank Kabel]

Hi
maybe something like the following in B1
=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))
and copy down for all rows

Note: if A1 = 29 Feb. 2004 the result would be 1. March 2003

 

[Harlan Grove]

maybe something like the following in B1
=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))
and copy down for all rows ....

Reread the OP's specs. The start date is *not* the same day of the month,
though that means it's 366 rather than 365 days previous (aside from leap
years). While the formula above could be adapted to

=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)-1)

that's a lot of function calls. It's possible

=A1-366-(DAY(A1)=DAY(A1-366))

would be sufficient.

 

[Frank Kabel]

Reread the OP's specs. The start date is *not* the same day of the month,
though that means it's 366 rather than 365 days previous (aside from leap
years). While the formula above could be adapted to

=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)-1)

that's a lot of function calls. It's possible

=A1-366-(DAY(A1)=DAY(A1-366))

would be sufficient.

Hi Harlan
thanks for the correction (misread the OP's example data). I like your
solution with less function calls
Frank