1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Increment date by weeks accounting for 5 week months

Discussion in 'Business Applications' started by dpic44, Mar 28, 2019.

Thread Status:
Not open for further replies.
Advertisement
  1. dpic44

    dpic44 Thread Starter

    Joined:
    Mar 8, 2019
    Messages:
    4
    I'm creating a list of dates by week, the first week of the year will be input manually and the rest of the year will automatically populate. Since there are 4 months that have 5 weeks I need to show 5 rows per month.

    I can get it to leave the 5th week blank on the first 4 week month, but I can't get the first week of the following month to populate correctly if it follows a 5 week month. I only want it to leave the 5th week blank if it's a 4 week month.

    =IFS(A4="",A3+7,AND(A4<>"",MONTH(A4+7)=MONTH(A4)),A4+7,MONTH(A4+7)<>MONTH(A4),"")

    Week
    01/07/2019
    01/14/2019
    01/21/2019
    01/28/2019
    blank
    02/04/2019
    02/11/2019
    02/18/2019
    02/25/2019
    blank
    03/04/2019
    03/11/2019
    03/18/2019
    03/25/2019
    blank
    04/01/2019
    04/08/2019
    04/15/2019
    04/22/2019
    04/29/2019

    Thanks for any help you can give me :)
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,211
    First Name:
    Wayne
    in A2 put the date 7/1/19
    then you can use in A3
    =IF(A2="",A1+7,IF(MONTH(A2)=MONTH(A2+7),A2+7,""))
    and copy down
    NOTE I'm in UK, so used UK based dates DD/MM/YY
    but should work OK in US dste format
     

    Attached Files:

  3. dpic44

    dpic44 Thread Starter

    Joined:
    Mar 8, 2019
    Messages:
    4
    Hi Wayne! Thanks for the quick reply. I did it this way first, where it breaks down is after a 5 week month. I don't want a space after a 5 week month, only a 4 week month (5 row block for each month). I'm working with a forecast and each month has 5 rows. This spreadsheet will be used for each year so the 5 week months will change and we only want to change the first date and have everything populate down from there. I should have been more explicit in my post.

    Here's the result from the formula above ... there is a blank after July but I don't want one there. I'm really puzzled :confused:
    06/04/2018
    06/11/2018
    06/18/2018
    06/25/2018
    Blank okay here
    07/02/2018
    07/09/2018
    07/16/2018
    07/23/2018
    07/30/2018
    Don't want blank here
    08/06/2018
    08/13/2018
    08/20/2018
    08/27/2018
    Blank okay here
    09/03/2018
    09/10/2018
    09/17/2018
    09/24/2018
    Blank okay here
    10/01/2018
    10/08/2018
    10/15/2018
    10/22/2018
    10/29/2018
    Don't want blank here
    11/05/2018
    11/12/2018
    11/19/2018
    11/26/2018
    Blank okay here
    12/03/2018
    12/10/2018
    12/17/2018
    12/24/2018
    12/31/2018
    Don't want blank here
    01/07/2019
    01/14/2019
    01/21/2019
    01/28/2019
    Blank okay here
    02/04/2019
    02/11/2019
    02/18/2019
    02/25/2019
    Blank okay here
    03/04/2019
    03/11/2019
    03/18/2019
    03/25/2019
    Blank okay here
    04/01/2019
    04/08/2019
    04/15/2019
    04/22/2019
    04/29/2019

    43591 This is the last row, not sure why I got this.
     
    Last edited: Mar 28, 2019
  4. dpic44

    dpic44 Thread Starter

    Joined:
    Mar 8, 2019
    Messages:
    4
    Maybe we need to check for the number of weeks in a month first, or use COUNT ...

    I created named ranges for the 5 rows in each month, Jun_Dates, Jul_Dates, etc. Is there a way to incorporate that into the formula?
     
    Last edited: Mar 28, 2019
  5. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,211
    First Name:
    Wayne
    IN A3 put
    =IF(A2="",A1+7,IF(OR(SUM(INT((WEEKDAY(A2-DAY(A2)+1-2)+(DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-1))/7))=5,MONTH(A2)=MONTH(A2+7)),A2+7,""))
    and copy down
    should work as required
     

    Attached Files:

    dpic44 likes this.
  6. dpic44

    dpic44 Thread Starter

    Joined:
    Mar 8, 2019
    Messages:
    4
    @etaf, I cannot thank you enough for helping me with this. It works like a charm. I tried it for fiscal year 6/2018-5/2019 and also for fiscal year 6/2019-5/2020. It is perfect.

    I have them putting the beginning of the fiscal year in A2. A4 is the first week of production so A4 = A2. Then I added your formula to A5 and copied it down. I'm locking all cells except A2 so it can't be changed. Thanks again!

    upload_2019-3-30_20-48-30.png
     
  7. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,211
    First Name:
    Wayne
    you are very welcome
     
  8. suckhoe24gio

    suckhoe24gio

    Joined:
    Mar 31, 2019
    Messages:
    2
    I should have been more explicit in my post.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1225053

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice