Round date to next half year
 

Hi Folks,
I need to some help from the more learned amongst us.

I have a simple sheet for recording document storage. Each row contaians a document number, the date of the document and the retention period. using edate i populate a field in the row with the retention end date. I run document disposals twice a year - nominally on 1st Jan and then on 1st July.

Is there a formula I could beg borrow or steal, that would look at my retention end date, and then decide which disposal cycle the document needs to fit into.

For instance if the retention end date is 23/02/21, the next disposal cycle will be July 21. If the retention end date was 30/08/21 then the next disposal cycle would be Jan 22 etc.

Doc No Doc Date Ret Retention End Disposal Cycle
4939849636 01/02/20 15 01/02/2035 07/35
4963750017 16/03/20 15 16/03/2035 07/35
5001850350 01/07/20 15 01/07/2035 01/36

Ret in the above example means Retention Period in years.

Many thanks for reading

Round date to next half year
 

Hi Mark,

Am Tue, 2 Feb 2021 01:35:52 -0800 (PST) schrieb Mark Sullivan:

Hi Folks,
I need to some help from the more learned amongst us.

I have a simple sheet for recording document storage. Each row contaians a document number, the date of the document and the retention period. using edate i populate a field in the row with the retention end date. I run document disposals twice a year - nominally on 1st Jan and then on 1st July.

Is there a formula I could beg borrow or steal, that would look at my retention end date, and then decide which disposal cycle the document needs to fit into.

For instance if the retention end date is 23/02/21, the next disposal cycle will be July 21. If the retention end date was 30/08/21 then the next disposal cycle would be Jan 22 etc.

Doc No Doc Date Ret Retention End Disposal Cycle
4939849636 01/02/20 15 01/02/2035 07/35
4963750017 16/03/20 15 16/03/2035 07/35
5001850350 01/07/20 15 01/07/2035 01/36

Ret in the above example means Retention Period in years.

try:
=IF(MONTH(D2)<7,DATE(YEAR(D2),7,1),DATE(YEAR(D2)+1 ,1,1))
and format the cell "MM/YY"


Regards
Claus B.
--
Windows10
Microsoft 365 for business

Round date to next half year
 

On Tuesday, 2 February 2021 at 09:56:02 UTC, wrote:
Hi Mark,
Am Tue, 2 Feb 2021 01:35:52 -0800 (PST) schrieb Mark Sullivan:

Hi Folks,
I need to some help from the more learned amongst us.

I have a simple sheet for recording document storage. Each row contaians a document number, the date of the document and the retention period. using edate i populate a field in the row with the retention end date. I run document disposals twice a year - nominally on 1st Jan and then on 1st July.

Is there a formula I could beg borrow or steal, that would look at my retention end date, and then decide which disposal cycle the document needs to fit into.

For instance if the retention end date is 23/02/21, the next disposal cycle will be July 21. If the retention end date was 30/08/21 then the next disposal cycle would be Jan 22 etc.

Doc No Doc Date Ret Retention End Disposal Cycle
4939849636 01/02/20 15 01/02/2035 07/35
4963750017 16/03/20 15 16/03/2035 07/35
5001850350 01/07/20 15 01/07/2035 01/36

Ret in the above example means Retention Period in years.
try:
=IF(MONTH(D2)<7,DATE(YEAR(D2),7,1),DATE(YEAR(D2)+1 ,1,1))
and format the cell "MM/YY"


Regards
Claus B.
--
Windows10
Microsoft 365 for business


Excellent Claus. Simple and works perfectly. Many thanks for the prompt solution.