What formula do I use to add to a date column in Excel?
 

Help! We have a date in the format 01/01/2000 in column A1. We need to
produce a date in column B1 that is five years on minus one day (i.e.
31/12/2005). Does anyone know of a way to do this?

Many thanks!

The date would be 31/12/2004, not 2005.

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

HTH
Jason
Atlanta, GA

-----Original Message-----
Help! We have a date in the format 01/01/2000 in column
A1. We need to
produce a date in column B1 that is five years on minus
one day (i.e.
31/12/2005). Does anyone know of a way to do this?

Many thanks!
.

Works perfectly, thank you very much!

Donna

"Jason Morin" wrote:

The date would be 31/12/2004, not 2005.

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

HTH
Jason
Atlanta, GA

-----Original Message-----
Help! We have a date in the format 01/01/2000 in column
A1. We need to
produce a date in column B1 that is five years on minus
one day (i.e.
31/12/2005). Does anyone know of a way to do this?

Many thanks!
.