Rounding dates to the next specific date

[RWJ67]

I am working on a spreadsheet for monitoring personnel records, especially in relation to the disposal of the records. I current have the spreadsheet which calculates 6 years after an employee left, and which will compare this to today's date to flag they have been gone for 6 years.

Our policy is to destroy records 6 years after end of the tax year in which they left. I am trying to achieve this by either:-

1. Round up the calculated 6 year date to the following 5th April
OR
2. Round up the leaving date to the following 5th April and then add the 6 years.

Either way is OK but its the rounding to the next specified date (ie:- the next 5th April) that's throwing me.

Example
Person leaves 1st July 2018, 6 years = 1 July 2024 = Report as 5th April 2025
Person leaves 1st March 2018, 6 years = 1 March 2024 = Report as 5th April 2024.

 

[footoo]

With leave date in A1, to return the disposal date :

=DATE(YEAR(A1)+6+(A1>DATE(YEAR(A1),4,5)),4,5)

 

[Snakehips]

@RWJ67

Maybe...

Excel 2010
	A	B
1	Left	Delete
2	01/07/2018	05/04/2025
3	01/03/2018	05/04/2024
4	01/04/2018	05/04/2024
5	06/04/2018	05/04/2025
6	05/04/2018	05/04/2024
Sheet2

Cell Formulas
Range	Formula
B2	=DATE(YEAR(A2)+6+MAX(SIGN(A2-DATE(YEAR(A2),4,5)),0),4,5)

Hope that helps.