Calculating a future dates based on dates or birth.

**CDobby** · 07-31-2012, 08:25 AM

I have a situation where I need to take a persons date of birth, and add depending on certain reasons either 24, 35 or 93 years to their date of birth, but then take that to the next 1st September (this will be a date an archive is destroyed). So a birth date of 25/10/2000 will be 01/09/2025 as will 01/04/2001 if having to use the 24 year rule. Is there away of doing this. I have looked at ways of breaking the date up into months and years etc and adding to it. It didn't work and was quite lengthy.

**daddylonglegs** · 07-31-2012, 08:39 AM

If you have a date in A2 this formula will add 24 years and then give the next 1st September

=DATE(YEAR(A2+121)+24,9,1)

**CDobby** · 07-31-2012, 09:05 AM

Thanks daddylonglegs.
That does add 24 years and sets the date as 1st Sept. But it will not create the same result for both examples I used above. It will return 01/09/2025 and 01/09/2024. For dates between 1st Sept and 31st Dec it will not carry it over to 1st Sept in the following year before adding 24 years.

**daddylonglegs** · 07-31-2012, 09:17 AM

Originally Posted by CDobby

.....But it will not create the same result for both examples I used above. It will return 01/09/2025 and 01/09/2024.

Did you verify that? When I use that formula with the dates you suggested I get the same result for both as required, 01/09/2025

The +121 part takes care of that (1st Sept to 31st dec is 121 days)

**CDobby** · 07-31-2012, 09:25 AM

Daddylonglegs
Sorry my mistake. It works fine. Gives right result every time. Very many thanks.

**Cutter** · 07-31-2012, 11:22 AM

@ CDobby

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.