1. Forum
  2. Usenet
  3. MSOFT.PUB.EXCEL.WORKSHEET

  • Round date to next half year

    From Claus Busch@21:1/5 to All on Tue Feb 2 10:55:59 2021
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Mark Sullivan@21:1/5 to All on Tue Feb 2 01:35:52 2021
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Mark Sullivan@21:1/5 to claus...@t-online.de on Tue Feb 2 03:58:13 2021
    On Tuesday, 2 February 2021 at 09:56:02 UTC, claus...@t-online.de 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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)