Calculating for a specific date range

C

Christy P

I have a column that lists a date of manufacture for an item. I need the next
colum to tell me the date 6 months before the date of manufacture in 4 years.
I then need it to tell me the date for 6 months after the date of
manufacture in 8 years. So for example, this units date of manufacture is
November of 2003. I need the next column to tell me 5/2007, then for the next
5/2008. Any help?
 
M

Mike H

Christy,

I've only included the example for the first cell but it's easy enough to
work out for the second.

=DATE(YEAR(A1)+4,MONTH(A1)-6, DAY(A1))

i.e. for May 2007 add 4 year and take away 6 months.

Mike
 
J

JE McGimpsey

Your description and your examples aren't consistant. I'll assume your
examples are what you want, and that you want a one-year interval
centered on 4 years from manufacture. Adjust to suit.

One way:

A1: 11/1/2003
B1: =DATE(YEAR(A1)+4,MONTH(A1)-6,DAY(A1))
C1: =DATE(YEAR(A1)+4,MONTH(A1)+6,DAY(A1))
 
P

Peo Sjoblom

One way

With your date in A1

=DATE(YEAR(A1)+4,MONTH(A1)-6,DAY(A1))

that should give you a clue how to get the 8 year 6 months after

Make sure the dates are dates and not text like November of 2003

If indeed you have text but it will always be name of month of year you can
use

=DATE(YEAR(LEFT(A1,FIND(" ",A1)-1)&" 1,
"&MID(A1,FIND("f",A1)+2,255))+4,MONTH(LEFT(A1,FIND(" ",A1)-1)&" 1,
"&MID(A1,FIND("f",A1)+2,255))-6,DAY(LEFT(A1,FIND(" ",A1)-1)&" 1,
"&MID(A1,FIND("f",A1)+2,255)))
 
Top