Trouble interpreting date range. Excel2007

[sdandolini]

I am trying to build an Equipment Replacement Schedule for computer hardware.

I have a column that reads the warranty start date and adds the number of years that has been determined as the replacement cycle. That looks like this:

=IF(ISBLANK(H6),"",DATE(YEAR(H6)+Summary!$C$18,MONTH(H6),DAY(H6)))

That returns a series of dates. If the warranty start date is 1/1/2005 and the cycle is 4 years, the date would be 1/1/2009.

We purchase our computers in 4 quarterly purchases. July 15th, November 15th, February 15th, and May 15th.

What I am trying to do is create a column that looks at that date and assigns the closest quarterly purchase.

I am hoping someone has some ideas for me.

Thank You !

 

[GlennUK]

The closest quarterly purchase before or after the calculated date?

 

[sdandolini]

The closest QP after would be my first choice, but not if it is a deal breaker.

 

[GlennUK]

Can you try this array formula ( entered using Ctrl-Shift-Enter instead of Enter ):

=MIN(IF(DAY(A3+ROW($1:$130)-1)=15,IF(MONTH(A3+ROW($1:$130)-1)={2,5,7,11},A3+ROW($1:$130)-1,99999),99999))

... replacing all occurences of A3 with the cell reference of your test date.

When a cell contains an array formula, Excel puts curly brackets "{}" around the formula, to indicate that it's an array formula.

 

[sdandolini]

Wow !

It works. You are amazing. Thanks!

I will have to take some time and try and figure out how this formula works.

Thanks again!

 

[GlennUK]

My pleasure

 

[barry houdini]

I think you could also use this non array version for the same result

=DATE(YEAR(A3),LOOKUP(A3,DATE(YEAR(A3),{0,2,5,7,11},16),{2,5,7,11,14}),15)

 

[barry houdini]

.....or a shorter version....

=LOOKUP(A3-1,DATE(YEAR(A3),{0,2,5,7,11;2,5,7,11,14},15))

 

[Fowmy]

.....or a shorter version....

=LOOKUP(A3-1,DATE(YEAR(A3),{0,2,5,7,11;2,5,7,11,14},15))

Wow,

This is a nice one.

what I can not understand is deducting 1 from A3 in "A3-1".

Could you explain if you do not mind?

 

[barry houdini]

Hello Fowmy,

If you don't subtract 1 then any date that is exactly on one of the purchase dates will be "bumped" to the next purchase date, e.g. if A3 is 15th Nov 2010 then without the -1 you'd get the answer as 15th Feb 2011

In the previous version I got round that by using 16 and 15 but for the shorter version to work all the dates are 15th