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 !

 

[Fowmy]

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

Thanks Barry, I got it.

 

[sdandolini]

Thank you everyone! This is all very helpful. I am so glad this resource exists.

 

[GlennUK]

Am glad you got some useful knowhow ... I still learn things on here