Equal to Date Field Minus One Year

[Web-pass.com]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have two columns "last invoice date" and "next invoice date". About half of the entries don't have a 'last invoice date' but do have 'next invoice date'. For these entries the 'last invoice date' is equal to the 'next invoice date' just minus one year.. so the next invoice date would be something like 06/13/2010, and the last invoice date is blank but should be 06/13/2009. Is it possible to create some kind of an 'equal to - 1 year' formula? Any help is greatly appreciated. Thank you!

 

[macropod]

Hi Web-pass,

For a date in A1, try:
=MIN(DATE(YEAR(A1)-1,MONTH(A1)+{0,1},DAY(A1)*{1,0}))
or
=MIN(DATE(YEAR(A1),MONTH(A1)-12+{0,1},DAY(A1)*{1,0}))

The complications are necessary to handle leap years correctly.

 

[Web-pass.com]

Thank you for the reply, it is appreciated. I will try that formula in a minute. I ended up copying the entire 'next invoice date' column, did a 'find & replace' for '2010' to '2009' and copied results into 'last invoice date' column. But I do love a good formula so I will try yours and let you know how it works. Thanks again!

Hi Web-pass,
>
> For a date in A1, try:
> =MIN(DATE(YEAR(A1)-1,MONTH(A1)+{0,1},DAY(A1)*{1,0}))
> or
> =MIN(DATE(YEAR(A1),MONTH(A1)-12+{0,1},DAY(A1)*{1,0}))
>
> The complications are necessary to handle leap years correctly.
>
> --
> Cheers
> macropod
> [Microsoft MVP - Word]
>
>
> wrote in message news:[email protected]...

> > Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have two columns "last invoice date" and "next invoice
> > date". About half of the entries don't have a 'last invoice date' but do have 'next invoice date'. For these entries the 'last
> > invoice date' is equal to the 'next invoice date' just minus one year.. so the next invoice date would be something like
> > 06/13/2010, and the last invoice date is blank but should be 06/13/2009. Is it possible to create some kind of an 'equal to - 1
> > year' formula? Any help is greatly appreciated. Thank you!

>
>

 

[Web-pass.com]

Thank you Macropod, that did work. I appreciate the reply and help

Hi Web-pass,
>
> For a date in A1, try:
> =MIN(DATE(YEAR(A1)-1,MONTH(A1)+{0,1},DAY(A1)*{1,0}))
> or
> =MIN(DATE(YEAR(A1),MONTH(A1)-12+{0,1},DAY(A1)*{1,0}))
>
> The complications are necessary to handle leap years correctly.
>
> --
> Cheers
> macropod
> [Microsoft MVP - Word]
>
>
> wrote in message news:[email protected]...

> > Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have two columns "last invoice date" and "next invoice
> > date". About half of the entries don't have a 'last invoice date' but do have 'next invoice date'. For these entries the 'last
> > invoice date' is equal to the 'next invoice date' just minus one year.. so the next invoice date would be something like
> > 06/13/2010, and the last invoice date is blank but should be 06/13/2009. Is it possible to create some kind of an 'equal to - 1
> > year' formula? Any help is greatly appreciated. Thank you!

>
>