Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 2nd 21, 10:35 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2021
Posts: 2
Default 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

  #2   Report Post  
Old February 2nd 21, 10:55 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,848
Default 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
  #3   Report Post  
Old February 2nd 21, 12:58 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2021
Posts: 2
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I round time down to the nearest half hour? KatJ Excel Worksheet Functions 3 September 4th 09 01:54 AM
Rounding Date to Next Half Year frrrrrr Excel Discussion (Misc queries) 3 July 17th 08 08:34 PM
Round up numbers to either a half or a whole depending on which side of 0.5 it is John Williams Excel Discussion (Misc queries) 10 November 25th 06 01:11 PM
how do i round time to the nearest half a minute ALEX Excel Discussion (Misc queries) 3 September 25th 06 11:25 PM
Round up to next half number rmb4253 New Users to Excel 7 March 23rd 06 10:51 AM


All times are GMT +1. The time now is 02:45 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017