date column based on existing date column

  • Thread starter Thread starter Michael Malinsky
  • Start date Start date
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
 
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
 
Frank Kabel said:
maybe something like the following in B1
=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))
and copy down for all rows ....
Click to expand...

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.
 
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.
Click to expand...


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