Return date if in range, else return blank

[LisaL]

My apologies in advance if too much information, just wanting to get
anything possible needed out there to save back and forth for the experts!


The first thing in the worksheet includes calculating the date of
anniversary for events based up on start date, adding the 25, 85 or 181
months to know when that next level kicks in. Currently, I am using the
following calculation, having to change the mmddyy fields in the first cell
of each row, then copy to the 85 & 180 months columns (I & J). Is there an
easier way?

Row Column B Column H Column I Column J
Column K
1
1/1/2009
2 25 85
181 12/31/2009
3 25-84 mos 85-180 mos 181 mos
4 Hire Date Level 1 Level 2 Level 3
2009
5 11/1/2007 12/1/2009 12/1/2014 12/1/2022

The calculation I'm using in H5 is:
=DATE(2007,11+H$2,1) then just copying it to the right

As such, for each row I have to manually change each mo dy yr - is there an
easier way?


The next challenge is for column K to look at the three columns to the left
(H, I & J) and IF the date falls within that calendar year (currently
manually entered in K1 1/1/2009 & K2 12/31/2009), THEN return that date, else
return blank.

There may be a way to just make it look at the year and IF 2009 (or that
year) then return the value, else blank but I can't seem to find that either.

Thanks so much in advance for any assistance! Let me know if I left out any
needed info.

LisaL

 

[Luke M]

In H5:
=DATE(YEAR($B5),MONTH($B5)+H$2,DAY($B5))
You can now copy across to column J, and down your rows w/o having to keep
changing formula.

for column K, assuming your always checking the current calendar year
=IF(YEAR(TODAY())=YEAR(H5),H5,IF(YEAR(TODAY())=YEAR(I5),I5,IF(YEAR(TODAY())=YEAR(J5),J5,"")))

If you want to continue to set the date/year to check, simply replace the
TODAY() functions with a cell reference.