Dates in Formulas

[Cortlyn]

Hi

I am trying to identify milestone anniversaries of 1,5,10,15,20,25,30,35,40 and over 40 from the date an employee joined the Company but only between the dates of 1st July 2009 to 30th June 2010. It's the split year part that I am having trouble with.

Thanks

Regards

C

[Richard Buttrey]

Hi,

With July 1st 2009 in A1 and 3o June 2010 in B1, and 1,5,10,15 etc.. in B2, C2, D2...etc. and joining dates in column A starting in A3. In B3 enter:

Please Login or Register  to view this content.

and copy across and down.

You could then analyse the resulting table with a Pivot Table and filter the Yes values

HTH

[Cortlyn]

Hi Richard

Thanks for your help but I think that I am not following you instructions correctly, please see attached file.

Thanks

Cortlyn

[DonkeyOte]

I think Richard simply mistyped a couple of references (ie not absolute).

His formula revised re: absolutes would be:

Please Login or Register  to view this content.

For your >40 column perhaps first

Please Login or Register  to view this content.

Followed up with

Please Login or Register  to view this content.

[Cortlyn]

Thank you Richard and DonkeyOte. It works!
Kind regards
Cortlyn

[daddylonglegs]

Here's a slightly different approach, assuming that you always want to search in a 1 year period.

With hire dates in A2 down, put the year end date in F1 (in your case 30th June 2010) and the list of significant anniversaries in H2:H20 (one per cell, e.g. 1,5,10,15,20,25 etc.)

Then in B2 copied down to show anniversary (if there is one)

=IF(A2>F$1,"",IF(COUNTIF(H$2:H$20,DATEDIF(A2,F$1,"y")),DATEDIF(A2,F$1,"y"),""))

and in C2 copied down to show the date of that anniversary

=IF(B2="","",DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2)))

See attached - I generated some random dates in column A, press F9 to re-generate

[Cortlyn]

Thank you Daddylonglegs

That gives it all in one column and makes it easier for me to count and Pivot.

Regards

Cortlyn